Reputation: 827
I have a table like so:
Name | Note |
--------------------------------------
John Doe | Smells like cheese |
Kelly Smith | Smells like ham |
Richmond White | Smells like apples |
John Doe | Sounds like trumpet |
Kelly Smith | sounds like tuba |
I found part of my answer here: delete duplicated row and merge cell value mysql , but that only returns the first occurence of the data in the second column. I need concatenate it like so:
Name | Note |
--------------------------------------
John Doe | Smells like cheese |
| Sounds like trumpet |
--------------------------------------
Kelly Smith | Smells like apples |
| Sounds like Tuba |
--------------------------------------
Furthemore, I'd like to keep the line break in the 'note' column.
Thanks in advance for any insight!
Upvotes: 0
Views: 615
Reputation: 92845
You can use GROUP_CONCAT()
for string aggregation like this
SELECT name, GROUP_CONCAT(Note) note
FROM table1
GROUP BY name
This will produce the following output:
+----------------+----------------------------------------+ | name | note | +----------------+----------------------------------------+ | John Doe | Smells like cheese,Sounds like trumpet | | Kelly Smith | Smells like ham,sounds like tuba | | Richmond White | Smells like apples | +----------------+----------------------------------------+
Here is SQLFiddle demo
Furthemore, I'd like to keep the line break in the 'note' column.
It's a matter of presentation and should be done in your client code. For example in php you can explode()
note
column values while you're iterating over the resultset and emit the proper line breaks (it may be \n
if it's in text mode, </br>
or separate cell in a table or div
or something else if it's HTML output).
But if you want to you change a delimiter to anything other than a comma (e.g. to \n
) you can do it with SEPARATOR
clause
GROUP_CONCAT(Note SEPARATOR '\n')
Upvotes: 2