How to concatenate multiple MySQL rows into one field?

Question

Using MySQL, I can do the following:

SELECT hobbies FROM peoples_hobbies WHERE person_id = 5;

My output:

shopping
fishing
coding

But I only want 1 row, 1 column:

Expected output:

shopping, fishing, coding

The reason is that I'm selecting multiple values ​​from multiple tables and after all the joins, I'm getting much more rows than I want.

I looked up a function on the MySQL Doc and it doesn't look like the CONCAT or CONCAT_WS functions accept a result set.

Does anyone here know how to do this?

Answer 1

See GROUP_CONCAT if your version of MySQL (4.1) supports it. For more information, see DocumentationDetails.

It looks like:

SELECT GROUP_CONCAT(hobbies SEPARATOR ', ') 
  FROM peoples_hobbies 
  WHERE person_id = 5 
  GROUP BY 'all';

Answer 2

You can use GROUP_CONCAT:

SELECT person_id,
   GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Ludwig stated in his comment, a> you can add the DISTINCT operator to avoid duplication:

SELECT person_id,
   GROUP_CONCAT(DISTINCT hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Jan mentioned in their comment, a> you can also sort the values ​​before imploding using ORDER BY:

SELECT person_id, 
       GROUP_CONCAT(hobbies ORDER BY hobbies ASC SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Dag stated in his comment, the result has a limit of 1024 bytes. To resolve this issue, run this query before querying:

SET group_concat_max_len = 2048;

Of course, you can change 2048 as needed. Calculate and assign values:

SET group_concat_max_len = CAST(
                     (SELECT SUM(LENGTH(hobbies)) + COUNT(*) * LENGTH(', ')
                           FROM peoples_hobbies
                           GROUP BY person_id) AS UNSIGNED);