MySQL – Grouping by Multiple Columns to Single Column as A String
- by Pinal Dave
In this post titled SQL SERVER – Grouping by Multiple Columns to Single Column as A String we have seen how to group multiple column data in comma separate values in a single row grouping by another column by using FOR XML clause.
In this post we will see how we can produce the same result using the GROUP_CONCAT function in MySQL.
Let us create the following table and data.
CREATE TABLE TestTable (ID INT, Col VARCHAR(4));
INSERT INTO TestTable (ID, Col)
SELECT 1, 'A'
UNION ALL
SELECT 1, 'B'
UNION ALL
SELECT 1, 'C'
UNION ALL
SELECT 2, 'A'
UNION ALL
SELECT 2, 'B'
UNION ALL
SELECT 2, 'C'
UNION ALL
SELECT 2, 'D'
UNION ALL
SELECT 2, 'E';
Now to generate csv values of the column col for each ID, use the following code
SELECT ID, GROUP_CONCAT(col) AS CSV FROM TestTable
GROUP BY ID;
The result is
ID CSV
1 A,B,C
2 A,B,C,D,E
You can also change the delimiters. For example instead of comma, if you want to have a pipe symbol (|), use the following
SELECT ID, REPLACE(GROUP_CONCAT(col),',','|') AS CSV FROM TestTable
GROUP BY ID;
The result is
ID CSV
1 A|B|C
2 A|B|C|D|E
MySQL makes this very simple with its support of GROUP_CONCAT function.
Reference: Pinal Dave (http://blog.sqlauthority.com)Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL
