Is it possible 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 one row and one column:

Desired output:

shopping, fishing, coding

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

I looked up a function in the MySQL documentation and it looks like the CONCAT or CONCAT_WS functions don't accept a result set.

So does anyone here know how to do this?

Answer 1

If your MySQL version (4.1) supports it, you can check GROUP_CONCAT. Please refer to Documentation for more details.

The query statement is as follows:

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

Answer 2

You can use the GROUP_CONCAT function:

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

As Ludwig mentioned in his comment, 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, you can also sort the values ​​before merging, 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, there is a 1024 byte limit on the result. To resolve this issue, run the following query before your query:

SET group_concat_max_len = 2048;

Of course, you can change the value of 2048 as needed. Here's how to calculate and assign a value:

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