In MariaDB, we can use the GROUP_CONCAT() function to return our query results as a comma separated list. By that I mean, for a given column, we can convert all rows into a single row that contains a comma separated list of the values that made up that column. Each row is a separate item in the list.

Example

Suppose we run the following query:

SELECT meal 
FROM guest_meals;

Result:

+---------+
| meal    |
+---------+
| Salad   |
| Fish    |
| Burrito |
| Pasta   |
| Salad   |
| Salad   |
+---------+
6 rows in set (0.001 sec)

That query resulted in six rows.

We can convert those rows into one comma separated row like this:

SELECT GROUP_CONCAT(meal) 
FROM guest_meals;

Result:

+--------------------------------------+
| GROUP_CONCAT(meal)                   |
+--------------------------------------+
| Salad,Fish,Burrito,Pasta,Salad,Salad |
+--------------------------------------+
1 row in set (0.003 sec)

Change the Delimiter

We can specify our own separator by passing it to the function, prefixed with the SEPARATOR keyword:

SELECT GROUP_CONCAT(meal SEPARATOR ' + ') 
FROM guest_meals;

Result:

Salad + Fish + Burrito + Pasta + Salad + Salad

Ordering the Results

We can order the results by using an ORDER BY clause in the function:

SELECT GROUP_CONCAT(meal ORDER BY meal ASC) 
FROM guest_meals;

Result:

Burrito,Fish,Pasta,Salad,Salad,Salad

Return Only Unique Values

We can use a DISTINCT clause to return only unique values:

SELECT GROUP_CONCAT(DISTINCT meal ORDER BY meal ASC) 
FROM guest_meals;

Result:

Burrito,Fish,Pasta,Salad

Limit the Results

We can use a LIMIT clause to limit the number of items in the list:

SELECT GROUP_CONCAT(DISTINCT meal ORDER BY meal ASC LIMIT 3) 
FROM guest_meals;

Result:

Burrito,Fish,Pasta

Grouped Queries

The GROUP_CONCAT() function can be handy when running grouped queries. See MariaDB GROUP_CONCAT() for an example.