MySQL GROUP_CONCAT()

In SQL language, the GROUP_CONCAT() function allows you to group the non-zero values of a group into a string. This is useful to group results in a single line as long as there are results in this group.

The most obvious advantage of this function is to concatenate the values of a column corresponding to a join with another table. In this way, it is not always necessary to carry out additional processing of the results to use the values.
 

 

Syntax:

This function can be used in SQL queries such as this one:

SELECT `id`, GROUP_CONCAT( `column_name` )
FROM `table`
GROUP BY `id`

In this SQL query, the values of the column “column_name” will be concatenated and separated by default by a comma.

It is possible to choose the character that is used as a separator by respecting the following syntax:

SELECT `id`, GROUP_CONCAT( `column_name` SEPARATOR ' ' )
FROM `table`
GROUP BY `id`

In this second SQL query, the values will be separated by a space.
 

Example :

Let’s consider the following table:
 
Products table

+----+---------+-----------+---------------------------------+
| id |   name  | category  |          description            |
+----+---------+-----------+---------------------------------+
| 1  | Pencil  | furniture | used to take notes              |
| 2  | Gum     | furniture | used to remove marks from paper |
| 3  | Modem   | computer  | allows connection to Internet   |
| 4  | Speaker | computer  | receive audio                   |
+----+---------+-----------+---------------------------------+

 

 

List categories and concatenate description:

It is possible to list all the categories and to use GROUP_CONCAT() function to display each of the titles associated with these categories, separated by a comma. It is, therefore, possible to use the following SQL query:

SELECT `category`, GROUP_CONCAT(`description`) AS description_concat
FROM `Products` 
GROUP BY `category`

Output:

+-----------+----------------------------------------------------+
| category  |                    description                     |
+-----------+----------------------------------------------------+
| furniture | used to take notes used to remove marks from paper |
| computer  | allows connection to Internet receive audio        |
+-----------+----------------------------------------------------+

The result of this SQL query shows that it is possible to use the GROUP_CONCAT() function to group all the descriptions of each of these categories in a single column.
MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More