NguyenStorm NguyenStorm - 4 months ago 8
MySQL Question

combine 2 values to 1 column mysql

SELECT s.*, k.address FROM student s
INNER JOIN address k
ON instr(s.fulladdress, k.address) > 0
WHERE j.phonetic like '%california%'


this result will display 2 row with 2 part of address:

30,Andre Hans, Address 1 Address 2,class IBA1001, Address 1

30,Andre Hans, Address 1 Address 2,class IBA1001, Address 2


I want to display it in 1 row:

30,Andre Hans, Address 1 Address 2,class IBA1001, Address 1 | Address 2


How i can do that?

JPG JPG
Answer

Use group by and group_concat:

SELECT s.*, group_concat(k.address separator ' | ') FROM student s
INNER JOIN address k
ON instr(s.fulladdress, k.address) > 0
WHERE j.phonetic like '%california%' 
GROUP BY s.col1, s.col2 ....  -- all the columns in table student
Comments