GROUP_CONCAT

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.

Syntax

  1. > GROUP_CONCAT(expr)

The full syntax is as follows:

  1. GROUP_CONCAT([DISTINCT] expr [,expr ...]
  2.              [ORDER BY {unsigned_integer | col_name | expr}
  3.                  [ASC | DESC] [,col_name ...]]
  4.              [SEPARATOR str_val])

Arguments

ArgumentsDescription
exprRequired. It specifies one or more columns or expressions to join.
DISTINCTOptional. To eliminate duplicate values.
ORDER BYOptional. To sort values in the result. To sort in reverse order, add the DESC (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.
SEPARATOROptional. The default separator between values in a group is comma (,).

Returned Value

The return value is a nonbinary or binary string, depending on whether the arguments are nonbinary or binary strings.

It returns NULL if there are no non-NULL values.

Examples

  1. create table t1(a int,b text,c text);
  2. insert into t1 values(1,"a","bc"),(2,"ab","c"),(3,"aa","bb"),(3,"aa","bb");
  4. mysql> select group_concat(distinct a,b,c separator '|') from t1;
  5. +-----------------------------------+
  6. | group_concat(distinct a, b, c, |) |
  7. +-----------------------------------+
  8. | 1abc|2abc|3aabb                   |
  9. +-----------------------------------+
  10. 1 row in set (0.01 sec)
  12. mysql> select group_concat(distinct b,c separator '|') from t1 group by a;
  13. +--------------------------------+
  14. | group_concat(distinct b, c, |) |
  15. +--------------------------------+
  16. | abc                            |
  17. | abc                            |
  18. | aabb                           |
  19. +--------------------------------+
  20. 3 rows in set (0.01 sec)
  22. mysql> select group_concat(distinct b,c separator '|') from t1;
  23. +--------------------------------+
  24. | group_concat(distinct b, c, |) |
  25. +--------------------------------+
  26. | abc|abc|aabb                   |
  27. +--------------------------------+
  28. 1 row in set (0.01 sec)