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 is determined by the group_concat_max_len server system variable, which defaults to 1024 bytes.
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])
DISTINCT
eliminates duplicate values from the output string.
ORDER BY
determines the order of returned values (see SELECT
syntax for details).
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.
Examples
SELECT student_name, GROUP_CONCAT(test_score) FROM student GROUP BY student_name;
Get a readable list of MariaDB users:
SELECT GROUP_CONCAT(User SEPARATOR '\n') FROM mysql.user;
Get a readable list of hosts from which each user can connect:
SELECT User, GROUP_CONCAT(Host SEPARATOR ', ') FROM mysql.user GROUP BY user;