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

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]
             [LIMIT {[offset,] row_count | row_count OFFSET offset}])

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.001 sec)

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

To achieve this, all we need to do is 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.003 sec)

Ordering

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

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

Result:

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

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.

Limiting the Output

We can use the LIMIT clause to limit how many items are included in the list:

SELECT GROUP_CONCAT(PetName LIMIT 3)
FROM Pets;

Result:

Fluffy,Fetch,Scratch

Any ordering is applied before the LIMIT clause:

SELECT GROUP_CONCAT(PetName ORDER BY PetName DESC LIMIT 3)
FROM Pets;

Result:

Wag,Tweet,Scratch

Note that the LIMIT clause is only supported from MariaDB 10.3.3.

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

The maximum returned length in bytes is determined by the group_concat_max_len server system variable, which defaults to 1M (in MariaDB 10.2.4 and higher) or 1K (in MariaDB 10.2.3 and lower). If group_concat_max_len is 512 or lower, the return type is VARBINARY or VARCHAR; otherwise, the return type is BLOB or TEXT. The choice between binary or non-binary types depends from the input.

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.