MySQL | GROUP_CONCAT method
Start your free 7-days trial now!
MySQL's GROUP_CONCAT(~) method concatenates non-NULL values from the same group together.
The maximum allowed length of the concatenated value is controlled by system variable group_concat_max_len, which has a default value of 1024.
Syntax
SELECT column1, GROUP_CONCAT(DISTINCT column2 ORDER BY column2 ASC|DESC SEPARATOR ' ')FROM tableGROUP BY column2
Examples
Consider the following table about whether students hold a drivers license:
student_id | full_name | age | has_license |
|---|---|---|---|
1 | Sky Towner | 17 | 0 |
2 | Ben Davis | 19 | 1 |
3 | Travis Apple | 18 | 0 |
4 | Arthur David | 16 | 0 |
5 | Benjamin Town | 17 | 1 |
The above sample table can be created using the code here.
Basic usage
To concatenate the full names of students who are the same age:
SELECT age, GROUP_CONCAT(full_name)FROM drivers_licenseGROUP BY age;
+------+--------------------------+| age | GROUP_CONCAT(full_name) |+------+--------------------------+| 16 | Arthur David || 17 | Sky Towner,Benjamin Town || 18 | Travis Apple || 19 | Ben Davis |+------+--------------------------+
As Sky Towner and Benjamin Town are both 17, we return the two names concatenated.
DISTINCT
To group by has_license and concatenate distinct ages in each group:
SELECT has_license, GROUP_CONCAT(DISTINCT age)FROM drivers_licenseGROUP BY has_license;
+-------------+----------------------------+| has_license | GROUP_CONCAT(DISTINCT age) |+-------------+----------------------------+| 0 | 16,17,18 || 1 | 17,19 |+-------------+----------------------------+
ORDER BY
To specify the order in which values are concatenated:
SELECT age, GROUP_CONCAT(full_name ORDER BY student_id DESC)FROM drivers_licenseGROUP BY age;
+------+--------------------------------------------------+| age | GROUP_CONCAT(full_name ORDER BY student_id DESC) |+------+--------------------------------------------------+| 16 | Arthur David || 17 | Benjamin Town,Sky Towner || 18 | Travis Apple || 19 | Ben Davis |+------+--------------------------------------------------+
In age group 17, the student with larger student id, 'Benjamin Town' is concatenated first followed by 'Sky Towner'.
SEPARATOR
To use ' / ' as the separator for concatenation:
SELECT age, GROUP_CONCAT(full_name SEPARATOR ' / ')FROM drivers_licenseGROUP BY age;
+------+-----------------------------------------+| age | GROUP_CONCAT(full_name SEPARATOR ' / ') |+------+-----------------------------------------+| 16 | Arthur David || 17 | Sky Towner / Benjamin Town || 18 | Travis Apple || 19 | Ben Davis |+------+-----------------------------------------+
Note that concatenated records are now separated by ' / '.
