Concatenating multiple rows of a result set

15/02/2009

This is needed when you need to concatenate multiple rows of a result set in MySQL.

CREATE TABLE services (
  id INT UNSIGNED NOT NULL,
  client_id INT UNSIGNED NOT NULL,
  KEY (id)
);

INSERT INTO services
VALUES (1,1),(1,2),(3,5),(3,6),(3,7);

SELECT id,client_id 
       FROM services 
       WHERE id = 3;

+----+-----------+
| id | client_id 
+----+-----------+
|  3 |		 5 
|  3 |		 6 
|  3 |		 7 
+----+-----------+

SELECT id,GROUP_CONCAT(client_id) 
      FROM services 
      WHERE id = 3 GROUP BY id;

+----+-------------------------+
| id | GROUP_CONCAT(client_id) 
+----+-------------------------+
|  3 | 5,6,7				   
+----+-------------------------+

The group_concat function is affected by the group_concat_max_len and the max_allowed_packet variables. So, if you need to concatenate more than mysql can handle by default(1024 bytes) you just change the max_allowed_packet in my.cnf and set this:

SET [SESSION | GLOBAL] group_concat_max_len = VALUE;

into your session.

There are 9 comments in this article:

  1. 6/09/2014(877) 957-6677 say:

    Appreciating the dedication you put into your website and detailed
    information you offer. It’s good to come across a blog every once in a while that isn’t the same unwanted
    rehashed material. Great read! I’ve bookmarked your site and I’m adding your RSS feeds
    to my Google account.

  2. 14/02/2015Friendly Windows Guide say:

    Friendly Windows Guide

    Concatenating multiple rows of a result set – Yak Shaving

  3. 25/02/2015Bellesse SKin say:

    Bellesse SKin

    Concatenating multiple rows of a result set – Yak Shaving

  4. 25/02/2015Healthy Colon Plus Review say:

    Healthy Colon Plus Review

    Concatenating multiple rows of a result set – Yak Shaving

  5. 18/03/20156live say:

    6live

    Concatenating multiple rows of a result set – Yak Shaving

  6. 23/03/2015Tim Decapua say:

    Tim Decapua

    Concatenating multiple rows of a result set – Yak Shaving

  7. 5/07/2015lose 10 pounds fast diet say:

    lose 10 pounds fast diet

    Concatenating multiple rows of a result set – Yak Shaving

  8. 5/07/2015plexus slim weight loss tips around the world say:

    plexus slim weight loss tips around the world

    Concatenating multiple rows of a result set – Yak Shaving

  9. 14/09/2015porn movie dastan sex ba khahar say:

    porn movie dastan sex ba khahar

    Concatenating multiple rows of a result set – Yak Shaving

Write a comment: