What is the GROUP_CONCAT function in MySQL?
GROUP_CONCAT is a function which concatenates/merges the data from multiple rows into one field. It is a GROUP BY function which returns a string if the group contains at least 1 non-null value, if it does not, it returns a Null value.
As you can see in the diagram above, the hexagons are concatenated into groups based on the color of the circles.
Clauses of GROUP_CONCAT:
There are 3 clauses of GROUP_CONCAT
-
DISTINCT: This clause eliminates the repeated values in the result. -
ORDER BY: This clause concatenates the values after sorting them. -
SEPARATOR: This clause automatically separates the values by,operator. If we wish to separate the values by a different operator, we would pass the operator in the string literal.
Sample table of “Employees”
| emp_id | first_name | last_name | quality | dep_id |
|---|---|---|---|---|
| 1 | George | Cleverly | Speaks well | 123 |
| 2 | Tom | Rooney | Manages well | 123 |
| 1 | George | Cleverly | Punctual | 123 |
| 3 | Clarke | James | Quick worker | 451 |
| 3 | Clarke | James | Manages well | 451 |
| 4 | Hill | Billings | Quick worker | 451 |
Examples
1. Simple GROUP_CONCAT:
This example concatenates the qualities of each employee into a single field.
SELECT emp_id, first_name, last_name, dep_id,GROUP_CONCAT( quality ) as "qualities"FROM Employee group by emp_id;
2. Distinct clause:
This example concatenates the distinct qualities for each department.
SELECT dep_id,GROUP_CONCAT( DISTINCT quality)as "Employee qualities"from Employee group by dep_id;
3. ORDER BY and SEPARATOR clause:
Here the Separator ‘_’ will separate the values with an underscore (_), and a space before and after '_’.
SELECT dep_id,GROUP_CONCAT( DISTINCT emp_id ORDER BY emp_id SEPARATOR' _ ')as "Employee ids"from Employee group by dep_id;
Free Resources