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 );
mysql> SELECT c1, c2, ....., cN GROUP_CONCAT ( [DISTINCT] c_name1 [ORDER BY] [SEPARATOR] ) FROM table_name GROUP BY c_name2;
In this syntax,
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.
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:
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.