How to Use GROUP_CONCAT in MySQL: Syntax and Examples

In MySQL, use the GROUP_CONCAT() function to combine values from multiple rows into a single string, separated by commas or a custom delimiter. It is often used with GROUP BY to aggregate grouped data into one row per group.

Syntax

The GROUP_CONCAT() function syntax is:

• GROUP_CONCAT([DISTINCT] expression [ORDER BY expression ASC|DESC] [SEPARATOR 'separator'])

Explanation:

• expression: The column or value to concatenate.
• DISTINCT: Optional, removes duplicate values.
• ORDER BY: Optional, sorts values before concatenation.
• SEPARATOR: Optional, defines the string between values (default is comma).

sql

GROUP_CONCAT([DISTINCT] column_name ORDER BY column_name ASC SEPARATOR ', ')

Example

This example shows how to list all products per category in one row using GROUP_CONCAT() with GROUP BY.

sql

CREATE TABLE products (
  id INT,
  category VARCHAR(20),
  product_name VARCHAR(50)
);

INSERT INTO products VALUES
(1, 'Fruits', 'Apple'),
(2, 'Fruits', 'Banana'),
(3, 'Fruits', 'Orange'),
(4, 'Vegetables', 'Carrot'),
(5, 'Vegetables', 'Broccoli');

SELECT category, GROUP_CONCAT(product_name ORDER BY product_name ASC SEPARATOR ', ') AS products
FROM products
GROUP BY category;

Output

category | products
-------- | -----------------------
Fruits   | Apple, Banana, Orange
Vegetables | Carrot, Broccoli

Common Pitfalls

Common mistakes when using GROUP_CONCAT() include:

• Not using GROUP BY when expecting grouped results.
• Ignoring the default maximum length of the result (1024 characters), which can truncate output.
• Forgetting to specify ORDER BY if order matters.

To avoid truncation, increase group_concat_max_len:

sql

SET SESSION group_concat_max_len = 10000;

Quick Reference

Feature	Description	Default
DISTINCT	Remove duplicate values	No
ORDER BY	Sort values before concatenation	No
SEPARATOR	String between values	Comma (',')
Max Length	Maximum length of result string	1024 characters

Key Takeaways

Use GROUP_CONCAT() to combine multiple row values into one string per group.

Always use GROUP BY to group rows before concatenation.

Specify ORDER BY inside GROUP_CONCAT() to control value order.

Increase group_concat_max_len if output is truncated.

Use SEPARATOR to customize the delimiter between concatenated values.