How to use GROUP BY to concatenate strings in MySQL and SQL Server?

By Prabhakaran S · September 2023

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;;