How can I concatenate set of results in MySQL?

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.