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 full syntax is as follows:
GROUP_CONCAT([DISTINCT]expr
[,expr
...] [ORDER BY {unsigned_integer
|col_name
|expr
} [ASC | DESC] [,col_name
...]] [SEPARATORstr_val
])
mysql> SELECT student_name, -> GROUP_CONCAT(test_score) -> FROM student -> GROUP BY student_name;
Or:
mysql> SELECT student_name, -> GROUP_CONCAT(DISTINCT test_score -> ORDER BY test_score DESC SEPARATOR ' ') -> FROM student -> GROUP BY student_name;
In MySQL, you can get the concatenated values of expression combinations. To eliminate duplicate values, use the DISTINCT
clause. To sort values in the result, use the ORDER BY
clause. To sort in reverse order, add theDESC
(descending) keyword to the name of the column you are sorting by in the ORDER BY
clause. The default is ascending order; this may be specified explicitly using the ASC
keyword. The default separator between values in a group is comma (“,
”). To specify a separator explicitly, use SEPARATOR
followed by the string literal value that should be inserted between group values. To eliminate the separator altogether, specify SEPARATOR ''
.
The result is truncated to the maximum length that is given by the group_concat_max_len
system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet
. The syntax to change the value ofgroup_concat_max_len
at runtime is as follows, where val
is an unsigned integer:
SET [GLOBAL | SESSION] group_concat_max_len = val
;
The return value is a nonbinary or binary string, depending on whether the arguments are nonbinary or binary strings. The result type is TEXT
or BLOB
unless group_concat_max_len
is less than or equal to 512, in which case the result type is VARCHAR
or VARBINARY
. (Prior to MySQL 5.0.19, GROUP_CONCAT()
returned TEXT
or BLOB
group_concat_max_len
greater than 512 only if the query included an ORDER BY
clause.)
quoted from:
https://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html#function_group-concat