Syntax
------

GROUP_CONCAT(expr)

Description
-----------

This function returns a string result with the concatenated non-NULL values
from a group. It returns NULL if there are no non-NULL values.

The maximum returned length in bytes is determined by the group_concat_max_len
server system variable, which defaults to 1M (>= MariaDB 10.2.4) or 1K (<=
MariaDB 10.2.3).

If group_concat_max_len <= 512, 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.

The full syntax is as follows:

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}])

DISTINCT eliminates duplicate values from the output string.

ORDER BY determines the order of returned values.

SEPARATOR specifies a separator between the values. The default separator is a
comma (,). It is possible to avoid using a separator by specifying an empty
string.

LIMIT
-----

MariaDB starting with 10.3.3
----------------------------
Until MariaDB 10.3.2, it was not possible to use the LIMIT clause with
GROUP_CONCAT. This restriction was lifted in MariaDB 10.3.3.

Examples
--------

SELECT student_name,
   GROUP_CONCAT(test_score)
   FROM student
   GROUP BY student_name;

Get a readable list of MariaDB users from the mysql.user table:

SELECT GROUP_CONCAT(DISTINCT User ORDER BY User SEPARATOR '\n')
 FROM mysql.user;

In the former example, DISTINCT is used because the same user may occur more
than once. The new line (\n) used as a SEPARATOR makes the results easier to
read.

Get a readable list of hosts from which each user can connect:

SELECT User, GROUP_CONCAT(Host ORDER BY Host SEPARATOR ', ') 
 FROM mysql.user GROUP BY User ORDER BY User;

The former example shows the difference between the GROUP_CONCAT's ORDER BY
(which sorts the concatenated hosts), and the SELECT's ORDER BY (which sorts
the rows).

From MariaDB 10.3.3, LIMIT can be used with GROUP_CONCAT, so, for example,
given the following table:

CREATE TABLE d (dd DATE, cc INT);

INSERT INTO d VALUES ('2017-01-01',1);
INSERT INTO d VALUES ('2017-01-02',2);
INSERT INTO d VALUES ('2017-01-04',3);

the following query:

SELECT SUBSTRING_INDEX(GROUP_CONCAT(CONCAT_WS(":",dd,cc) ORDER BY cc
DESC),",",1) FROM d;
+----------------------------------------------------------------------------+
| SUBSTRING_INDEX(GROUP_CONCAT(CONCAT_WS(":",dd,cc) ORDER BY cc DESC),",",1) |
+----------------------------------------------------------------------------+
| 2017-01-04:3                                                               |
+----------------------------------------------------------------------------+

can be more simply rewritten as:

SELECT GROUP_CONCAT(CONCAT_WS(":",dd,cc) ORDER BY cc DESC LIMIT 1) FROM d;
+-------------------------------------------------------------+
| GROUP_CONCAT(CONCAT_WS(":",dd,cc) ORDER BY cc DESC LIMIT 1) |
+-------------------------------------------------------------+
| 2017-01-04:3                                                |
+-------------------------------------------------------------+

URL: https://mariadb.com/kb/en/group_concat/