GROUP_CONCAT

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                                                |
+-------------------------------------------------------------+

See Also

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.

© 2021 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/group_concat/