Skip to content
Related Articles

Related Articles

MySQL | Group_CONCAT() Function
  • Difficulty Level : Expert
  • Last Updated : 11 Mar, 2019
GeeksforGeeks - Summer Carnival Banner

The GROUP_CONCAT() function in MySQL is used to concatenate data from multiple rows into one field. This is an aggregate (GROUP BY) function which 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;



col1, col2, ...colN : These are the column names of 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:

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

Examples:

Let, consider an “Employee” table:



emp_idfnamelnamedept_idstrength
1mukeshgupta2Leadership
3neelamsharma3Hard-working
1mukeshgupta2Responsible
2deveshtyagi2Punctuality
3neelamsharma3Self-motivated
1mukeshgupta2Quick-learner
4keshavsinghal3Listening
2deveshtyagi2Quick-learner
5tanyajain1Hard-working
4keshavsinghal3Critical thinking
5tanyajain1Goal-oriented

Queries:

  1. Using simple GROUP_CONCAT() function-
    SELECT emp_id, fname, lname, dept_id, 
    GROUP_CONCAT ( strength ) as "strengths" 
    FROM employee group by emp_id;
    

    Output:

    emp_idfnamelnamedept_idstrengths
    1mukeshgupta2Leadership, Resposible, Quick-learner
    2deveshtyagi2Punctuality, Quick-learner
    3neelamsharma3Hard-working, Self-motivated
    4keshavsinghal3Listening, Critical thinking
    5tanyajain1Hard-working, Goal-oriented

  2. Using a DISTINCT clause-
    SELECT dept_id, 
    GROUP_CONCAT ( DISTINCT strength) 
    as "employees strengths"  
    from employee group by dept_id;
    

    Output:



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

  3. Use of ORDER BY clause:
    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_idemployees ids
    15
    21, 2
    33, 4

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

Til 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 BYy emp_id )as emp GROUP BY dept_id;

Explanation:

The above query consist of two SELECT statements an inner one and the outer one.

The inner SELECT statement –

SELECT dept_id, concat ( emp_id, ':',
GROUP_CONCAT ( strength separator ', ' ) ) as "strengths"  
FROM employee GROUP BY 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_idstrengths
21: Leadership, Responsible, Quick-learner
22: Punctuality, Quick-learner
33: Hard-working, Self-motivated
34: Listening, Critical thinking
15: Hard-working, Goal-oriented

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

Output:

dept_idemp-id : strengths
15: Hard-working, Goal-oriented
21: Leadership, Responsible, Quick-learner 2:Punctuality, Quick-learner
33: 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 sytem 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.

Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up
Recommended Articles
Page :