You can use GROUP_CONCAT:

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

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

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

As Jan stated in their comment, you can also sort the values before imploding it using ORDER BY:

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

As Dag stated in his comment, there is a 1024 byte limit on the result. To solve this, run this query before your query:

SET group_concat_max_len = 2048;

Of course, you can change 2048 according to your needs. To calculate and assign the value:

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

Have a look at GROUP_CONCAT if your MySQL version (4.1) supports it. See the documentation for more details.

It would look something like:

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

Alternate syntax to concatenate multiple, individual rows

WARNING: This post will make you hungry.

Given:

I found myself wanting to select multiple, individual rows—instead of a group—and concatenate on a certain field.

Let's say you have a table of product ids and their names and prices:

+------------+--------------------+-------+| product_id | name               | price |+------------+--------------------+-------+|         13 | Double Double      |     5 ||         14 | Neapolitan Shake   |     2 ||         15 | Animal Style Fries |     3 ||         16 | Root Beer          |     2 ||         17 | Lame T-Shirt       |    15 |+------------+--------------------+-------+

Then you have some fancy-schmancy ajax that lists these puppies off as checkboxes.

Your hungry-hippo user selects 13, 15, 16. No dessert for her today...

Find:

A way to summarize your user's order in one line, with pure mysql.

Solution:

Use GROUP_CONCAT with the the IN clause:

mysql> SELECT GROUP_CONCAT(name SEPARATOR ' + ') AS order_summary FROM product WHERE product_id IN (13, 15, 16);

Which outputs:

+------------------------------------------------+| order_summary                                  |+------------------------------------------------+| Double Double + Animal Style Fries + Root Beer |+------------------------------------------------+

Bonus Solution:

If you want the total price too, toss in SUM():

mysql> SELECT GROUP_CONCAT(name SEPARATOR ' + ') AS order_summary, SUM(price) AS total FROM product WHERE product_id IN (13, 15, 16);+------------------------------------------------+-------+| order_summary                                  | total |+------------------------------------------------+-------+| Double Double + Animal Style Fries + Root Beer |    10 |+------------------------------------------------+-------+