GROUP_CONCAT in MySQL

In SQL databases, we frequently need to manipulate and manage data. MySQL, one of the most popular database management systems, provides various functions to help us perform these tasks efficiently. One such function is the GROUP_CONCAT function.

The GROUP_CONCAT function in MySQL is used to concatenate data from multiple rows into one field. This is especially useful in multi-value output situations where traditional group by statements would result in multiple rows of data for each group.

Syntax of GROUP_CONCAT

Here is the basic syntax of the GROUP_CONCAT function:

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

• DISTINCT: It is used to remove duplicate entries.
• expr: This is the column or data you want to concatenate.
• ORDER BY: It is used to sort the concatenated data.
• SEPARATOR: It allows you to add a separator between the concatenated data. The default separator is comma (,).

Examples of GROUP_CONCAT

Simple Example of GROUP_CONCAT

For a simple example, consider a table named Students with the following data:

If we want to list all subjects for each student, we can use GROUP_CONCAT as follows:

SELECT Name, GROUP_CONCAT(Subject)
FROM Students
GROUP BY Name;

GROUP_CONCAT with DISTINCT

Consider a table Orders with the following data:

We can list distinct products for each customer using GROUP_CONCAT with DISTINCT as follows:

SELECT Customer, GROUP_CONCAT(DISTINCT Product)
FROM Orders
GROUP BY Customer;

GROUP_CONCAT with ORDER BY

If we want to order the concatenated list, we can do so with ORDER BY. Using the same Orders table as above, we can order the products alphabetically as follows:

SELECT Customer, GROUP_CONCAT(Product ORDER BY Product ASC)
FROM Orders
GROUP BY Customer;

GROUP_CONCAT with SEPARATOR

If we want to use a separator other than a comma, we can use SEPARATOR. Let's say we want to separate the products with a semicolon:

SELECT Customer, GROUP_CONCAT(Product SEPARATOR ';')
FROM Orders
GROUP BY Customer;

References

https://www.mysqltutorial.org/mysql-group_concat/