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.

Answer 1

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

See GROUP_CONCAT.