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.