How can I concatenate set of results in MySQL?

Question

I would like to join results returned in the set in MySQL with a comma as a separator string.

For example, set returned contains:

COLUMN_X
john
jerry
maria
joseph
gugla

I would like to receive the result as:

COLUMN_X-concat
john,jerry,maria,joseph,gugla

is that possible? thanks.

SELECT CONCAT(rooms.ID,",") FROM rooms AS rooms LEFT JOIN inter AS i ON rooms.ID=i.value WHERE xxx=999

doesn't work as I would like it to as it returns separate results.

Stefan Gehrig · Accepted Answer · 2015-12-09T20:21:01.407

80

SELECT GROUP_CONCAT(COLUMN_X SEPARATOR ',') FROM <<table>> GROUP BY NULL

edited Dec 09 '15 at 20:21

answered Nov 13 '09 at 10:38

Stefan Gehrig

When I run this query against a table of my own, I receive the error `Can't group on 'GROUP_CONCAT(id SEPARATOR ',')'`. Any idea why? – Nate Dec 09 '15 at 16:35
1

@Nate: Show your query. – Stefan Gehrig Dec 09 '15 at 16:36
`SELECT GROUP_CONCAT(p.id SEPARATOR ',') FROM products p GROUP BY 1` – Nate Dec 09 '15 at 16:41
@Nate: Try `... GROUP BY NULL` instead. – Stefan Gehrig Dec 09 '15 at 20:21
If like me, you got several columns to group, use DISTINCT to get rid of duplicates ( cf http://stackoverflow.com/questions/4561650/mysql-group-concat-duplicates) – Balmipour Mar 29 '16 at 17:11
@StefanGehrig it doesn't work in SQuirrel (SQL client version 3.1.2), do you have any solution for it? – Amita Patil Jun 19 '17 at 08:24
Error: 'GROUP_CONCAT' is not recognized as a function or procedure. – Amita Patil Jun 19 '17 at 11:37
@AmitaPatil: What MySQL version do you use? And you're sure that you're using MySQL at all? – Stefan Gehrig Jun 19 '17 at 14:29

1 Answers1