MySQL – Grouping by Multiple Columns to Single Column as A String

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;

MySQL - Grouping by Multiple Columns to Single Column as A String mysqlcsv-1

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;

MySQL - Grouping by Multiple Columns to Single Column as A String mysqlcsv-2

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 (https://blog.sqlauthority.com)

Previous Post
SQLAuthority News – Great Time Spent at Great Indian Developers Summit 2014
Next Post
SQL SERVER – SELECT INTO with FileGroup or Partitionis Not Possible

Related Posts

No results found.

4 Comments. Leave new

  • Hi,
    This is easier than what i know in Sql server.There is no such keyword in sql server .
    SELECT ID, stuff((Select ‘,’+col from @TestTable a1 where a.ID=a1.ID for xml path(”) ),1,1,”) AS CSV FROM @TestTable a
    GROUP BY ID;

    Reply
  • Hi,

    You may want to note that GROUP_CONCAT natively allows a custom seperator.
    You can see this here:

    So instead of using REPLACE(GROUP_CONCAT(col),’,’,’|’), you should instead use GROUP_CONCAT(col SEPARATOR ‘|’).

    This is better as it will not replace the characters if the column data happens to contains ‘,’

    Reply
  • How To do in in SQL?

    Reply

Leave a Reply

Menu