SQL GROUP_CONCAT()

What is GROUP_CONCAT()?

The GROUP_CONCAT() function lets you concatenate column values from multiple rows into a single string. It’s often used with GROUP BY to join the list of column values for rows within each group.

An example should make it clearer.

Example usage

Suppose you had a students table like the below:

student_id	subject
1	Maths
1	Science
2	English
3	Math
3	English

student_id	subjects
1	Math, Science
2	English
3	Math, English

You can use GROUP_CONCAT to achieve get the desired result:

SELECT 
  student_id, 
  GROUP_CONCAT(subject) as subjects
FROM 
  students
GROUP BY 
  student_id;

By default, GROUP_CONCAT() will separate each column value with a comma but you can also specify a different separator like so:

 GROUP_CONCAT(subject SEPARATOR '; ') as subjects

Specify order of concatenated values

Suppose you have a table called :

You can use GROUP_CONCAT with ORDER BY to concatenate employee names in a specific order within each department:

SELECT 
    department,
    GROUP_CONCAT(employee_name ORDER BY employee_name ASC SEPARATOR ', ') AS employee_list
FROM 
    employees
GROUP BY 
    department;

Result:

Use a different separator

By default, GROUP_CONCAT() uses a comma (,) as a separator but you can use a custom separator using the SEPARATOR keyword.

SELECT 
    department_id,
    GROUP_CONCAT(employee_name SEPARATOR ' | ') AS employee_list
FROM employees
GROUP BY department_id;

The above query will return the employee_list column as a string of names separated by a pipe (|) instead of the default comma.

Concatenate only unique values

Suppose you have a table called orders:

To get a concatenated list of unique names:

SELECT 
    GROUP_CONCAT(DISTINCT product_name ORDER BY product_name ASC SEPARATOR ', ') AS unique_products
FROM 
    orders;

Result: