MySQL | Group_CONCAT() Function
Last Updated :
15 Sep, 2023
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:
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 emp_id, fname, lname, dept_id;
Output
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
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
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_CONCATt(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-
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
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.
Like Article
Suggest improvement
Share your thoughts in the comments
Please Login to comment...