GROUP_CONCAT
Aggregate function. Concatenates all of the values passed in during aggregation.
Notice
This aggregate function is not to be confused with CONCAT, which is a non-aggregate function returning a concatenation of its list of arguments.
Syntax
GROUP_CONCAT( [DISTINCT]
expression, [ expression, [expression ...]]
[ORDER BY expression [ASC|DESC]]
[SEPARATOR string] )
Arguments
DISTINCT: optionally filter the result set for only unique sets of expressions
expression: any set of expressions. This may be a column name, the result of another function, or a math operation.
ORDER BY: optionally sort the result set of the GROUP_CONCAT values in ascending or descending order. By default, the result is sorted in ascending order.
SEPARATOR string: by default, the separator character is the comma “,”. This argument overrides the default.
Note: You cannot use the DISTINCT keyword with the ORDER BY clause in a GROUP_CONCAT function.
Return Type
The return type is a string. The result is made up of the concatenated non-NULL values from the given group, or NULL if there are no non-NULL values. The variable group_concat_max_len controls the maximum length string this function can return.
For more information on this variable, see the List of Engine Variables.
Examples
SELECT GROUP_CONCAT(ship_code) FROM products; **** +-------------------------+ | GROUP_CONCAT(ship_code) | +-------------------------+ | A,B,A,A,C,D,A,B | +-------------------------+
SELECT GROUP_CONCAT(DISTINCT ship_code) AS distinct_codes FROM products; **** +----------------+ | distinct_codes | +----------------+ | A,B,C,D | +----------------+
SELECT GROUP_CONCAT(DISTINCT ship_code SEPARATOR " ") AS distinct_codes FROM products; **** +----------------+ | distinct_codes | +----------------+ | A B C D | +----------------+
SELECT GROUP_CONCAT(first_name, " ", last_name) FROM BFFs; **** +------------------------------------------+ | GROUP_CONCAT(first_name, " ", last_name) | +------------------------------------------+ | Cecelia Cruz,Ari Floo,Skylar Rhodes | +------------------------------------------+
SELECT dept_id, GROUP_CONCAT(emp_id ORDER BY emp_id) FROM grptable GROUP_BY dept_id; **** +---------+--------------------------------------+ | dept_id | GROUP_CONCAT(emp_id ORDER BY emp_id) | +---------+--------------------------------------+ | 1 | 100 | | 5 | 500,700 | | 4 | 300,400 | | 6 | 600 | +---------+--------------------------------------+