MySQL | Group_CONCAT() Function

The GROUP_CONCAT() function in MySQL is used to concatenate data from multiple rows into one field. This is an aggregate (GROUP BY) function that returns a String value if the group contains at least one non-NULL value. Otherwise, it returns NULL.

Syntax:

SELECT col1, col2, ..., colN
GROUP_CONCAT ( [DISTINCT] col_name1 
[ORDER BY clause]  [SEPARATOR str_val] ) 
FROM table_name GROUP BY col_name2;

Parameters:

• col1, col2, …colN: These are the column names of the table.
• col_name1: Column of the table whose values are concatenated into a single field for each group.
• table_name: Name of table.
• col_name2: Column of the table according to which grouping is done.

Use of various clauses inside GROUP_CONCAT() function

• Distinct: It eliminates the repetition of values from the result.
• Order By: It sorts the values of the group in a specific order and then concatenates them.
• Separator: By default, the values of the group are separated by the (, ) operator. In order to change this separator value, a Separator clause is used followed by a string literal. It is given as Separator ‘str_value’.

Examples:

Let, consider an “Employee” table:

emp_id	fname	lname	dept_id	strength
1	mukesh	gupta	2	Leadership
3	neelam	sharma	3	Hard-working
1	mukesh	gupta	2	Responsible
2	devesh	tyagi	2	Punctuality
3	neelam	sharma	3	Self-motivated
1	mukesh	gupta	2	Quick-learner
4	keshav	singhal	3	Listening
2	devesh	tyagi	2	Quick-learner
5	tanya	jain	1	Hard-working
4	keshav	singhal	3	Critical thinking
5	tanya	jain	1	Goal-oriented

Query

1. Using simple GROUP_CONCAT() function –

SELECT emp_id, fname, lname, dept_id, 
GROUP_CONCAT ( strength ) as "strengths" 
FROM employee 
GROUP BY fname;

Output

emp_id	fname	lname	dept_id	strengths
1	mukesh	gupta	2	Leadership, Responsible, Quick-learner
2	devesh	tyagi	2	Punctuality, Quick-learner
3	neelam	sharma	3	Hard-working, Self-motivated
4	keshav	singhal	3	Listening, Critical thinking
5	tanya	jain	1	Hard-working, Goal-oriented

2. Using a DISTINCT clause –

Query

SELECT dept_id, 
GROUP_CONCAT ( DISTINCT strength)  as "employees strengths"  
FROM employee 
GROUP BY dept_id;

Output

dept_id	employees strengths
1	Goal-oriented, Hard-working
2	Leadership, Punctuality, Quick-learner, Responsible
3	Critical thinking, Hard-working, Listening, Self-motivated

3. Use of ORDER BY clause –

Query

SELECT dept_id, 
GROUP_CONCAT ( DISTINCT emp_id ORDER BY emp_id  SEPARATOR ', ')  as "employees ids" 
FROM employee 
GROUP BY dept_id;

Here, Separator ‘, ‘ will separate the values by a comma (, ) and a whitespace character.

Output

dept_id	employees ids
1	5
2	1, 2
3	3, 4

How to concatenate multiple rows of different columns in a single field?

Till now we have seen the use of GROUP_CONCAT() function to group the values of multiple rows that belongs to same column. But, using concat() function and group_concat() function together, we can combine more than one column values of different rows into single field.

Example:
Considering above table “employee”, if we wish to find employees strength along with employees id in second query then it is written as-

SELECT dept_id, GROUP_CONCAT ( strengths SEPARATOR '  ') as "emp-id : strengths"
FROM ( 
                SELECT dept_id, CONCAT ( emp_id, ':', GROUP_CONCAT(strength SEPARATOR', ') ) 
                as "strengths" 
                FROM employee 
                GROUP BY emp_id 
) as emp 
GROUP BY dept_id;

Explanation:
The above query consists of two SELECT statements an inner one and the outer one.

The inner SELECT statement-

Query

SELECT dept_id, concat ( emp_id, ':', GROUP_CONCAT ( strength separator ', ' ) ) as "strengths"  
FROM employee 
GROUP BY dept_id, emp_id

It will group the rows of employee table according to “emp_id”. The first resulted column displays dept_id, second column displays emp_id along with their strengths list.

Output for inner SELECT statement-

dept_id	strengths
2	1: Leadership, Responsible, Quick-learner
2	2: Punctuality, Quick-learner
3	3: Hard-working, Self-motivated
3	4: Listening, Critical thinking
1	5: Hard-working, Goal-oriented

The outer SELECT statement will now group these rows according to “dept_id”.

Output

dept_id	emp-id: strengths
1	5: Hard-working, Goal-oriented
2	1: Leadership, Responsible, Quick-learner 2:Punctuality, Quick-learner
3	3: Hard-working, Self-motivated 4:Listening, Critical thinking

Note: The result of GROUP_CONCAT() function is truncated to the maximum length i.e 1024 which is given by system variable group_concat_max_len. However, the value of group_concat_max_len variable can be changed at runtime by using SETcommand as-

SET [GLOBAL | SESSION] group_concat_max_len = value;
value: It is the new value set to the variable.