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

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

Try Hands-On: Fiddle

Create Input Table: Gist

Desired Output

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

Recommended Courses

1. SQL for Data Science – Level 1
2. SQL for Data Science – Level 2
3. SQL for Data Science – Level 3

Recommended Tutorial

1. Introduction to SQL
2. SQL Window Functons – Made Simple and Easy
3. SQL Subquery

More SQL Questions

1. How to concatenate multiple rows into one field in MySQL?
2. How to efficiently convert rows to columns in SQL?
3. How to find duplicate values in SQL?
4. How to select first row in each GROUP BY group?