GROUP_CONCAT() Function

The GROUP_CONCAT() function in MySQL is a type of an aggregate function. This function is used to concatenate string from multiple rows into a single string using various clauses. If the group contains at least one non-null value, it always returns a string value. Otherwise, you will get a null value.

The following are the syntax of the GROUP_CONCAT() function:

GROUP_CONCAT(  

    DISTINCT expression  

    ORDER BY expression  

    SEPARATOR sep  

); 

OR,

mysql> SELECT c1, c2, ....., cN  

GROUP_CONCAT (   

   [DISTINCT] c_name1   

   [ORDER BY]    

   [SEPARATOR] )   

FROM table_name GROUP BY c_name2; 

In this syntax,

• The c1, c2,….,cN are the table columns.
• The c_name1 is the table column whose values will be concatenated into a single string for each group.
• The c_name2 is the table column from which grouping is performed.

The options of GROUP_CONCAT() function are explained below:

Distinct: This clause removes the duplicate values in the group before doing concatenation.

Order By: It allows us to sorts the group data in ascending or descending order and then perform concatenation. By default, it performs the sorting in the ascending order. But, you can sort values in descending order using the DESC option explicitly.

Separator: By default, this clause uses comma(,) operator as a separator. If you want to change the default separator, you can specify the literal value.

NOTE: This function always returns a result in binary or non-binary string value that depends on the specified arguments. By default, it returns maximum length of string value equal to 1024. If you want to increase this length, you can use the group_concat_max_len system variable.

GROUP_CONCAT() Example

Let us create a table employee to understand how this function works in MySQL using different queries.

1. Using a Simple Query

mysql> SELECT emp_id, emp_fname, emp_lname, dept_id,   

GROUP_CONCAT(designation) as "designation" FROM employee group by emp_id;  

This statement will give the following output:

2. Using DISTINCT Clause

mysql> SELECT emp_fname, dept_id,   

GROUP_CONCAT(DISTINCT designation) as "designation" FROM employee group by emp_id; 

After successful execution of the above statement, we will get the following output:

3. Using Separator Clause

mysql>SELECT emp_fname,   

GROUP_CONCAT(DISTINCT designation SEPARATOR '; ') as "designation" FROM employee group by emp_id;   

Here, the separator clause changes the default returning string comma(,) to a semicolon(;) and a whitespace character.

The above statement will give the following output:

GROUP_CONCAT() and CONCAT_WS()

Now, you are aware of the working of the GROUP_CONCAT() function. Sometimes, we can use this function with the CONCAT_WS() function that gives the more useful result. The following statement explains it more clearly:

mysql>SELECT GROUP_CONCAT(CONCAT_WS(', ', emp_lname, emp_fname) SEPARATOR ';') as employeename FROM employee;  

In this statement, the CONCAT_WS() function first concatenates the first name and last name of each employee and results in the full name of the employees. Next, we use the GROUP_CONCAT() function with a semicolon (;) separator clause to make the list of all employees in a single row. Finally, execute the statement. After successful execution, we will get the following output:

This function returns the result in a single row, not a list of values. Therefore, we cannot work GROUP_CONCAT() function with the IN operator. If we use an IN operator with this function, then the query will not work because the IN operator accepts a list of values, not a string.