MySQL has a GROUP_CONCAT() function that enables us to return columns from a query as a delimited list.

It returns a string result with the concatenated non-NULL values from a group.

Syntax

The syntax goes like this:

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

Example

Suppose we run the following query:

SELECT PetName 
FROM Pets;

And we get the following result:

+---------+
| PetName |
+---------+
| Fluffy  |
| Fetch   |
| Scratch |
| Wag     |
| Tweet   |
| Fluffy  |
| Bark    |
| Meow    |
+---------+
8 rows in set (0.00 sec)

We can use GROUP_CONCAT() to return all of those rows as a delimited list.

To do this, we need to pass the PetName column as an argument to the GROUP_CONCAT() function:

SELECT GROUP_CONCAT(PetName) 
FROM Pets;

Result:

+-------------------------------------------------+
| GROUP_CONCAT(PetName)                           |
+-------------------------------------------------+
| Fluffy,Fetch,Scratch,Wag,Tweet,Fluffy,Bark,Meow |
+-------------------------------------------------+
1 row in set (0.01 sec)

Ordering

We can use the ORDER BY clause to order the output of this function:

SELECT GROUP_CONCAT(PetName ORDER BY PetName ASC)
FROM Pets;

Result:

Bark,Fetch,Fluffy,Fluffy,Meow,Scratch,Tweet,Wag

Note that this only sorts the output of the GROUP_CONCAT() function – it’s completely independent of any ordering applied to the SELECT statement itself.

The DISTINCT Clause

We can use the DISTINCT clause to return unique values. In other words, if there are duplicate values, only one occurrence is returned:

SELECT GROUP_CONCAT(DISTINCT PetName ORDER BY PetName ASC)
FROM Pets;

Result:

Bark,Fetch,Fluffy,Meow,Scratch,Tweet,Wag

In this case, Fluffy only appears once. When we run it without the DISTINCT clause, Fluffy appears twice.

Changing the Separator

By default, the list uses the comma as the delimiter. But we can change this if we like:

SELECT GROUP_CONCAT(PetName SEPARATOR '-')
FROM Pets;

Result:

Fluffy-Fetch-Scratch-Wag-Tweet-Fluffy-Bark-Meow

We can even use an empty string to remove all separators (so that the values are concatenated):

SELECT GROUP_CONCAT(PetName SEPARATOR '')
FROM Pets;

And we get the following result:

FluffyFetchScratchWagTweetFluffyBarkMeow

Grouped Query Results

We can include GROUP_CONCAT() in a query with a GROUP BY clause to achieve a result like this:

SELECT 
    PetTypeId,
    GROUP_CONCAT(PetName ORDER BY PetName ASC)
FROM Pets
GROUP BY PetTypeId
ORDER BY PetTypeId;

Result:

+-----------+--------------------------------------------+
| PetTypeId | GROUP_CONCAT(PetName ORDER BY PetName ASC) |
+-----------+--------------------------------------------+
|         1 | Tweet                                      |
|         2 | Fluffy,Meow,Scratch                        |
|         3 | Bark,Fetch,Fluffy,Wag                      |
+-----------+--------------------------------------------+

In my database, the actual pet type names are in another table called PetTypes. We could therefore run an INNER JOIN on the PetTypes table to get the actual pet type names:

SELECT 
    pt.PetType,
    GROUP_CONCAT(p.PetName ORDER BY p.PetName ASC)
FROM Pets p
INNER JOIN PetTypes pt ON
p.PetTypeId = pt.PetTypeId
GROUP BY pt.PetType
ORDER BY pt.PetType ASC;

Result:

+---------+------------------------------------------------+
| PetType | GROUP_CONCAT(p.PetName ORDER BY p.PetName ASC) |
+---------+------------------------------------------------+
| Bird    | Tweet                                          |
| Cat     | Fluffy,Meow,Scratch                            |
| Dog     | Bark,Fetch,Fluffy,Wag                          |
+---------+------------------------------------------------+

Length Limitations

GROUP_CONCAT()‘s output is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet.

You can check the current value like this:

SHOW VARIABLES LIKE '%group_concat%';

The syntax for changing this value goes as follows:

SET [GLOBAL | SESSION] group_concat_max_len = val;

Where val is an unsigned integer.