How to Return Query Results as a Comma Separated List in MySQL

Source: https://database.guide/how-to-return-query-results-as-a-comma-separated-list-in-mysql/

In MySQL, you can return your query results as a comma separated list by using the GROUP_CONCAT() function.

The GROUP_CONCAT() function was built specifically for the purpose of concatenating a query’s result set into a list separated by either a comma, or a delimiter of your choice.

This article provides examples of how it all works.

The Data

First, let’s use the following data in our first few examples:

USE Solutions;
SELECT TaskName
FROM Tasks;

Result:

+-------------------+
| TaskName          |
+-------------------+
| Do garden         |
| Feed cats         |
| Paint roof        |
| Take dog for walk |
| Relax             |
| Feed cats         |
+-------------------+

Basic Example

Here’s a basic example to demonstrate the GROUP_CONCAT() function:

SELECT GROUP_CONCAT(TaskName) 
FROM Tasks;

Result:

+------------------------------------------------------------------+
| GROUP_CONCAT(TaskName)                                           |
+------------------------------------------------------------------+
| Do garden,Feed cats,Paint roof,Take dog for walk,Relax,Feed cats |
+------------------------------------------------------------------+

As you can see, each row from the result set has been concatenated into a single row. By default, the list is separated by a comma.

Note that there are restrictions on how long this list can be. More about this later in the article.

Example – DISTINCT

You can use DISTINCT to remove duplicates (so that duplicate records become one record).

Example:

SELECT GROUP_CONCAT(DISTINCT TaskName) 
FROM Tasks;

Result:

+--------------------------------------------------------+
| GROUP_CONCAT(DISTINCT TaskName)                        |
+--------------------------------------------------------+
| Do garden,Feed cats,Paint roof,Relax,Take dog for walk |
+--------------------------------------------------------+

So in this case, “Feed cats” is only listed once, whereas it was listed twice in the previous example.

Example – ORDER BY

You can use ORDER BY to order the results by a given column.

Example:

SELECT GROUP_CONCAT(DISTINCT TaskName ORDER BY TaskName DESC) 
FROM Tasks;

Result:

+--------------------------------------------------------+
| GROUP_CONCAT(DISTINCT TaskName ORDER BY TaskName DESC) |
+--------------------------------------------------------+
| Take dog for walk,Relax,Paint roof,Feed cats,Do garden |
+--------------------------------------------------------+

So in this case I use DESC to specify that it should be in descending order. The alternative (and default) value is ASC for ascending.

Example – Specify a Delimiter

By default, the list is a comma-separated list. However, you can specify a delimiter of your choice if required.

To do this, use SEPARATOR followed by the string literal value that should be inserted between group values.

Example:

SELECT GROUP_CONCAT(DISTINCT TaskName SEPARATOR ' + ') 
FROM Tasks;

Result:

+----------------------------------------------------------------+
| GROUP_CONCAT(DISTINCT TaskName SEPARATOR ' + ')                |
+----------------------------------------------------------------+
| Do garden + Feed cats + Paint roof + Relax + Take dog for walk |
+----------------------------------------------------------------+

Example – Combining Columns

You can also concatenate columns, and provide their own separator by providing a string literal value.

Example:

SELECT GROUP_CONCAT(TaskId, ') ', TaskName SEPARATOR ' ') 
FROM Tasks;

Result:

+------------------------------------------------------------------------------------+
| GROUP_CONCAT(TaskId, ') ', TaskName SEPARATOR ' ')                                 |
+------------------------------------------------------------------------------------+
| 1) Do garden 2) Feed cats 3) Paint roof 4) Take dog for walk 5) Relax 6) Feed cats |
+------------------------------------------------------------------------------------+

In this example we return both the TaskId column and the TaskName column, separated by a closing parentheses and a space.  We also use the SEPARATOR argument to specify that the delimiter to be used between each (concatenated) row should be a space (instead of the default comma).

Grouped Results

The GROUP_CONCAT() function can be useful for occasions where you want to provide a list of results, grouped by another column.

For example, you might want a list of artists, with each artist followed by a list of albums they’ve released.

To demonstrate this, say we have a database with two tables; Artists and Albums. There is a one to many relationship between these tables. For every artist, there could be many albums.

So a regular query joining both tables might look something like this:

USE Music;
SELECT ar.ArtistName,
 al.AlbumName
FROM Artists ar
INNER JOIN Albums al
ON ar.ArtistId = al.ArtistId;

Result:

+------------------------+--------------------------+
| ArtistName             | AlbumName                |
+------------------------+--------------------------+
| Iron Maiden            | Powerslave               |
| AC/DC                  | Powerage                 |
| Jim Reeves             | Singing Down the Lane    |
| Devin Townsend         | Ziltoid the Omniscient   |
| Devin Townsend         | Casualties of Cool       |
| Devin Townsend         | Epicloud                 |
| Iron Maiden            | Somewhere in Time        |
| Iron Maiden            | Piece of Mind            |
| Iron Maiden            | Killers                  |
| Iron Maiden            | No Prayer for the Dying  |
| The Script             | No Sound Without Silence |
| Buddy Rich             | Big Swing Face           |
| Michael Learns to Rock | Blue Night               |
| Michael Learns to Rock | Eternity                 |
| Michael Learns to Rock | Scandinavia              |
| Tom Jones              | Long Lost Suitcase       |
| Tom Jones              | Praise and Blame         |
| Tom Jones              | Along Came Jones         |
| Allan Holdsworth       | All Night Wrong          |
| Allan Holdsworth       | The Sixteen Men of Tain  |
+------------------------+--------------------------+

As you can see, when using this format, if an artist has more than one album, that artist is listed out multiple times – once for each album.

We could modify this query so that each artist is only listed once. If an artist has more than one album, all albums are displayed in a single field within a comma separated list. We can do this thanks to the GROUP_CONCAT() function.

Example:

USE Music;
SELECT ar.ArtistName,
 GROUP_CONCAT(al.AlbumName)
FROM Artists ar
INNER JOIN Albums al
ON ar.ArtistId = al.ArtistId
GROUP BY ArtistName;

Result:

+------------------------+----------------------------------------------------------------------------+
| ArtistName             | GROUP_CONCAT(al.AlbumName)                                                 |
+------------------------+----------------------------------------------------------------------------+
| AC/DC                  | Powerage                                                                   |
| Allan Holdsworth       | All Night Wrong,The Sixteen Men of Tain                                    |
| Buddy Rich             | Big Swing Face                                                             |
| Devin Townsend         | Epicloud,Ziltoid the Omniscient,Casualties of Cool                         |
| Iron Maiden            | Somewhere in Time,Piece of Mind,Powerslave,Killers,No Prayer for the Dying |
| Jim Reeves             | Singing Down the Lane                                                      |
| Michael Learns to Rock | Eternity,Scandinavia,Blue Night                                            |
| The Script             | No Sound Without Silence                                                   |
| Tom Jones              | Long Lost Suitcase,Praise and Blame,Along Came Jones                       |
+------------------------+----------------------------------------------------------------------------+

Be Careful of the Length!

One important thing you need to be aware of when using GROUP_CONCAT() is that the result is truncated to the maximum length that is provided by the group_concat_max_len system variable, which has a default value of 1024.

This variable’s value can be set higher, by using the following syntax:

SET [GLOBAL | SESSION] group_concat_max_len = val;

Where val is an unsigned integer.

However, note that the effective maximum length of the return value is itself constrained by the value of max_allowed_packet.