How do you use the GROUP_CONCAT function in MySQL, and what is its purpose? — MySQL Interview Question #27

The ‘GROUP_CONCAT‘ function in MySQL is used to concatenate the values of a column from multiple rows into a single string. The concatenated string is returned as the output of the function.

The general syntax of the ‘GROUP_CONCAT‘ function is as follows:

SELECT GROUP_CONCAT(column_name SEPARATOR ',') FROM table_name GROUP BY group_column;

where ‘column_name‘ is the name of the column whose values need to be concatenated, ‘table_name‘ is the name of the table from which the data is being retrieved, ‘group_column‘ is the name of the column by which the data is being grouped, and ‘SEPARATOR‘ is an optional parameter that specifies the string to be used as a separator between the concatenated values. If the ‘SEPARATOR‘ parameter is not specified, a comma is used as the default separator.

Here is an example of how to use the ‘GROUP_CONCAT‘ function in MySQL. Suppose we have a table named ‘employees‘ with the following data:

| id | name  | department |
|----|-------|------------|
| 1  | John  | Sales      |
| 2  | Jane  | HR         |
| 3  | Sarah | Sales      |
| 4  | Mike  | Marketing |
| 5  | Alex  | Marketing |

If we want to concatenate the names of all employees in each department, we can use the following SQL statement:

SELECT department, GROUP_CONCAT(name SEPARATOR ', ') AS employees
FROM employees
GROUP BY department;

This will produce the following output:

| department | employees             |
|------------|----------------------|
| Sales      | John, Sarah           |
| HR         | Jane                 |
| Marketing | Mike, Alex            |

As we can see, the ‘GROUP_CONCAT‘ function has concatenated the names of all employees in each department into a single string, separated by a comma.

The ‘GROUP_CONCAT‘ function can also be used to concatenate values from multiple columns. For example, suppose we have a table named ‘orders‘ with the following data:

| id  | customer | product | quantity |
|----|----------|---------|----------|
| 1  | John     | iPhone  | 2        |
| 2  | Sarah    | iPad    | 1        |
| 3  | John     | MacBook | 3        |
| 4  | Mike     | iMac    | 2        |
| 5  | Alex     | iPhone  | 1        |

If we want to concatenate the ‘product‘ and ‘quantity‘ values for each customer, we can use the following SQL statement:

SELECT customer, GROUP_CONCAT(product, ' (', quantity, ') ' SEPARATOR ', ') AS orders
FROM orders
GROUP BY customer;

This will produce the following output:

| customer | orders                                |
|----------|---------------------------------------|
| John     | iPhone (2), MacBook (3) |
| Sarah    | iPad (1)                              |
| Mike     | iMac (2)                              |
| Alex     | iPhone (1)                            |

As we can see, the ‘GROUP_CONCAT‘ function has concatenated the ‘product‘ and ‘quantity‘ values for each customer into a single string, separated by a comma and enclosed in parenthesis.

In conclusion, the ‘GROUP_CONCAT‘ function in MySQL is a powerful tool for concatenating the values of a column from multiple rows into a single string. It is useful for generating summary reports and for simplifying the output of complex queries.