MySQL GROUP_CONCAT: Join Values from Many Rows into One

GROUP_CONCAT turns the values of a group into one text, like “all the tags of an article in one cell”.

query.sql

SQL

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price DECIMAL(8,2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    added_on DATE NOT NULL
);

INSERT INTO products (name, category, price, stock, added_on) VALUES
    ('Notebook',      'stationery',  80.00, 120, '2026-01-10'),
    ('Gel Pen',       'stationery',  15.50, 300, '2026-01-12'),
    ('Backpack',      'bags',       999.00,  25, '2026-02-01'),
    ('Marker Set',    'stationery', 145.00,   0, '2026-02-15'),
    ('Desk Lamp',     'home',       650.00,  40, '2026-03-03'),
    ('Water Bottle',  'home',       220.00,  75, '2026-03-20'),
    ('Laptop Sleeve', 'bags',       450.00,  18, '2026-04-02'),
    ('Sticky Notes',  'stationery',  35.00, 210, '2026-04-18');

query.sql

SQL

SELECT category, GROUP_CONCAT(name) AS product_names, COUNT(*) AS products
FROM products
GROUP BY category
ORDER BY category;

Output

OUTPUT

+------------+------------------------------------------+----------+
| category   | product_names                            | products |
+------------+------------------------------------------+----------+
| bags       | Backpack,Laptop Sleeve                   |        2 |
| home       | Desk Lamp,Water Bottle                   |        2 |
| stationery | Notebook,Gel Pen,Marker Set,Sticky Notes |        4 |
+------------+------------------------------------------+----------+
3 rows in set

Order, separator and distinct

query.sql

SQL

SELECT category,
       GROUP_CONCAT(name ORDER BY name SEPARATOR ' | ') AS sorted_names,
       GROUP_CONCAT(DISTINCT stock > 0 ORDER BY stock > 0) AS stock_flags
FROM products
GROUP BY category
ORDER BY category;

Output

OUTPUT

+------------+------------------------------------------------+-------------+
| category   | sorted_names                                   | stock_flags |
+------------+------------------------------------------------+-------------+
| bags       | Backpack | Laptop Sleeve                       | 1           |
| home       | Desk Lamp | Water Bottle                       | 1           |
| stationery | Gel Pen | Marker Set | Notebook | Sticky Notes | 0,1         |
+------------+------------------------------------------------+-------------+
3 rows in set

Syntax: GROUP_CONCAT([DISTINCT] expression [ORDER BY ...] [SEPARATOR 'text']). The default separator is a comma. Always specify the ORDER BY, because without it the order inside the text is not defined.

NULL values are skipped

GROUP_CONCAT ignores NULL. If every value in the group is NULL, the result is NULL.

The length limit

The result is cut at group_concat_max_len characters, which is 1024 by default. MySQL cuts silently, with only a warning:

query.sql

SQL

1
2
3

SET SESSION group_concat_max_len = 20;
SELECT GROUP_CONCAT(name ORDER BY name) AS cut_text FROM products;
SHOW WARNINGS;

Output

OUTPUT

+----------------------+
| cut_text             |
+----------------------+
| Backpack,Desk Lamp,G |
+----------------------+
1 row in set

+---------+------+---------------------------------+
| Level   | Code | Message                         |
+---------+------+---------------------------------+
| Warning | 1260 | Row 3 was cut by GROUP_CONCAT() |
+---------+------+---------------------------------+
1 row in set

The text was cut at 20 characters (Backpack,Desk Lamp,G). In a report that is a hidden data loss. Raise the limit for the session (SET SESSION group_concat_max_len = 100000;) before you run such a query, and keep it below max_allowed_packet.

query.sql

SQL

SET SESSION group_concat_max_len = 1024;

Not a data model

GROUP_CONCAT is for display and export. Do not store a comma-separated list in a column and then split it again (Splitting Text). Keep the rows, and make the list when you show it.

JSON aggregates (MySQL 8)

JSON_ARRAYAGG and JSON_OBJECTAGG build real JSON, with correct quoting, instead of text that you have to parse:

query.sql

SQL

SELECT category,
       JSON_ARRAYAGG(name) AS names_json,
       JSON_OBJECTAGG(name, price) AS prices_json
FROM products
WHERE category = 'bags'
GROUP BY category;

Output

OUTPUT

+----------+-------------------------------+-----------------------------------------------+
| category | names_json                    | prices_json                                   |
+----------+-------------------------------+-----------------------------------------------+
| bags     | ["Backpack", "Laptop Sleeve"] | {"Backpack": 999.00, "Laptop Sleeve": 450.00} |
+----------+-------------------------------+-----------------------------------------------+
1 row in set

(The order inside JSON_ARRAYAGG is not guaranteed, because it has no ORDER BY. If order matters, sort in a subquery or in the application.) JSON is the better choice when the application will parse the list: no problems with commas inside values.

A nested example: orders with their lines

query.sql

SQL

CREATE TABLE orders (id INT PRIMARY KEY, customer VARCHAR(20));
CREATE TABLE order_lines (order_id INT, item VARCHAR(20), quantity INT);
INSERT INTO orders VALUES (1, 'Maya'), (2, 'Rohan');
INSERT INTO order_lines VALUES (1, 'Notebook', 2), (1, 'Pen', 5), (2, 'Backpack', 1);

SELECT o.id, o.customer,
       GROUP_CONCAT(CONCAT(l.item, ' x', l.quantity) ORDER BY l.item SEPARATOR ', ') AS lines_text
FROM orders o JOIN order_lines l ON l.order_id = o.id
GROUP BY o.id, o.customer
ORDER BY o.id;

Output

OUTPUT

+----+----------+---------------------+
| id | customer | lines_text          |
+----+----------+---------------------+
|  1 | Maya     | Notebook x2, Pen x5 |
|  2 | Rohan    | Backpack x1         |
+----+----------+---------------------+
2 rows in set

A line-per-order summary for a report or an email, built in a single query.

dangerous

Watch the cut

A truncated GROUP_CONCAT is easy to miss. Check SHOW WARNINGS while testing, or set group_concat_max_len high enough for your data.