MySQL | Group_CONCAT() Function

The GROUP_CONCAT() function in MySQL is an aggregation function that combines data from multiple rows into a single string. It is particularly useful for aggregating summaries, such as combining related information into a single field for better readability or reporting.

In this article, we will explore the GROUP_CONCAT() function in detail, starting with its syntax and parameters to understand its structure and usage. We will provide examples demonstrating its functionality with key clauses like DISTINCT, ORDER BY, and SEPARATOR to showcase its flexibility.

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 of MySQL GROUP_CONCAT() Function

Let, consider an "Employee" table:

Example 1: Basic GROUP_CONCAT() Usage

Concatenate the strengths of employees grouped by their names.

Query:

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

Output:

Example 2: Using DISTINCT Clause

Query:

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

Output:

Example 3: Using 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_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-

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

Output:

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.

Conclusion

The GROUP_CONCAT() function in MySQL is an indispensable tool for aggregating data into a single field, offering flexibility with custom ordering, delimiters, and filtering. By understanding its syntax, parameters, and advanced use cases, you can streamline complex queries and enhance reporting capabilities.