GROUP_CONCAT

description

Syntax

VARCHAR GROUP_CONCAT([DISTINCT] VARCHAR str[, VARCHAR sep]) [ORDER BY { col_name | expr} [ASC | DESC])

This function is an aggregation function similar to sum (), and group_concat links multiple rows of results in the result set to a string. The second parameter, sep, is a connection symbol between strings, which can be omitted. Support Order By for sorting multi-row results, sorting and aggregation columns can be different. This function usually needs to be used with group by statements.

example

1. mysql> select value from test;
2. +-------+
3. | value |
4. +-------+
5. | a     |
6. | b     |
7. | c     |
8. | c     |
9. +-------+

11. mysql> select GROUP_CONCAT(value) from test;
12. +-----------------------+
13. | GROUP_CONCAT(`value`) |
14. +-----------------------+
15. | a, b, c, c              |
16. +-----------------------+

18. mysql> select GROUP_CONCAT(value, " ") from test;
19. +----------------------------+
20. | GROUP_CONCAT(`value`, ' ') |
21. +----------------------------+
22. | a b c c                     |
23. +----------------------------+

25. mysql> select GROUP_CONCAT(DISTINCT value) from test;
26. +-----------------------+
27. | GROUP_CONCAT(`value`) |
28. +-----------------------+
29. | a, b, c               |
30. +-----------------------+

32. mysql> select GROUP_CONCAT(value, NULL) from test;
33. +----------------------------+
34. | GROUP_CONCAT(`value`, NULL)|
35. +----------------------------+
36. | NULL                       |
37. +----------------------------+

39. SELECT abs(k3), group_concat(distinct cast(abs(k2) as varchar) order by abs(k1), k5) FROM bigtable group by abs(k3) order by abs(k3);     +------------+-------------------------------------------------------------------------------+
40. | abs(`k3`)  | group_concat(DISTINCT CAST(abs(`k2`) AS CHARACTER), ORDER BY abs(`k1`), `k5`) |
41. +------------+-------------------------------------------------------------------------------+
42. |        103 | 255                                                                           |
43. |       1001 | 1989, 1986                                                                    |
44. |       1002 | 1989, 32767                                                                   |
45. |       3021 | 1991, 32767, 1992                                                             |
46. |       5014 | 1985, 1991                                                                    |
47. |      25699 | 1989                                                                          |
48. | 2147483647 | 255, 1991, 32767, 32767                                                       |
49. +------------+-------------------------------------------------------------------------------+

1. ### keywords
2. GROUP_CONCAT,GROUP,CONCAT