Problem
Often when dealing with database records, you might encounter a situation where you want to group records based on a specific column but rather than aggregating the other columns using functions like SUM() or AVG(), you want to concatenate their strings.
MySQL has the GROUP_CONCAT() function for this purpose.
Input
ID | Class | Name |
---|---|---|
1 | 101 | Alice |
2 | 101 | Bob |
3 | 102 | Charlie |
4 | 103 | David |
5 | 101 | Eve |
6 | 102 | Frank |
Try Hands-On: Fiddle
Create Input Table: Gist
Desired Output
Class | Students |
---|---|
101 | Alice,Bob,Eve |
102 | Charlie,Frank |
103 | David |
Solution 1:
Using GROUP_CONCAT and GROUP BY
SELECT
Class,
GROUP_CONCAT(Name ORDER BY Name ASC) AS Students
FROM
Students
GROUP BY
Class;
Explanation:
The GROUP_CONCAT()
function in MySQL concatenates values from multiple rows into a single string.
You can also specify the order in which you’d like to concatenate the values using ORDER BY within the GROUP_CONCAT()
function.
So, for the given input table, when we group by the Class column, GROUP_CONCAT
() concatenates all student names associated with each class, separated by commas (by default). This provides a consolidated view of students in each class.
Solution for SQL Server:
Another common way to concatenate strings in SQL Server is to use XML PATH with the FOR XML clause.
Using COALESCE
SELECT
Class,
STUFF(
(SELECT ',' + s.Name
FROM Students s
WHERE s.Class = sc.Class
FOR XML PATH('')), 1, 1, '') AS Students
FROM
Students sc
GROUP BY
sc.Class;;