CODEWITHSUNDEEP
mysql
psorensen
psorensen

Reputation: 827

mysql Merge Cells where rows duplicate

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

Answers (1)

peterm
peterm

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 SEPARATORclause

GROUP_CONCAT(Note SEPARATOR '\n')

Upvotes: 2

Related Questions