MySQL GROUP_CONCAT() Function

The MySQL GROUP_CONCAT() function concatenates the values ​​of the columns or expressions from a group into a string and returns it.

GROUP_CONCAT() Syntax

Here is the syntax of the MySQL GROUP_CONCAT() function:

GROUP_CONCAT(
    [DISTINCT] expr [, expr2 ...]
    [ORDER BY ...]
    [SEPARATOR separator]
)

Typically, we use the GROUP_CONCAT() function like:

SELECT GROUP_CONCAT(expr), ...
FROM table_name
[WHERE ...]
[GROUP BY group_expr1, group_expr2, ...];

Parameters

expr [, expr2 ...]

Required. It specifies one or more columns or expressions to join.

ORDER BY

Optional. It is used to sort the values to be concatenated.

SEPARATOR separator

Optional. The separator to join values. Default is ,.

Return value

The MySQL GROUP_CONCAT(expr) function returns a string with concatenated non-NULL values sepcified by a column or expression from a group.

This function returns NULL if the result set has no rows.

GROUP_CONCAT() Examples

We’ll demonstrate with the student_score table .

First, let’s create the student_score table using the following statement:

DROP TABLE IF EXISTS `student_score`;
CREATE TABLE `student_score` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `subject` VARCHAR(255) NOT NULL,
    `score` INT NOT NULL
);

Here we have created a table to store students’ scores. This table consists of 4 columns:

• id - the primary key
• name - the student’s name
• subject - the subject
• score - the student’s grade of a subject

Second, let’s insert some rows for demonstration:

INSERT INTO `student_score` (`name`, `subject`, `score`)
VALUES
    ('Tom', 'Math', 80),
    ('Tom', 'English', 90),
    ('Tim', 'English', 98);

Here is the rows in the table:

+----+------+---------+-------+
| id | name | subject | score |
+----+------+---------+-------+
|  1 | Tom  | Math    |    80 |
|  2 | Tom  | English |    90 |
|  3 | Tim  | English |    98 |
+----+------+---------+-------+

Now, we use the GROUP_CONCAT() function to get a list of subjects for everyone, as follows:

SELECT
    name AS `Name`,
    GROUP_CONCAT(subject) AS `Subjects`
FROM student_score
GROUP BY name;

+------+--------------+
| Name | Subjects     |
+------+--------------+
| Tim  | English      |
| Tom  | Math,English |
+------+--------------+

Here, we group all rows by name and use the GROUP_CONCAT() function to concatenate the values of the subject column from each group.

In the above statement, since no delimiter is specified, a comma , is used for concatenation. If we want to use another delimiter (eg: /), specify it after SEPARATOR keyword:

SELECT
    name AS `Name`,
    GROUP_CONCAT(subject SEPARATOR '/') AS `Subjects`
FROM student_score
GROUP BY name;

+------+--------------+
| Name | Subjects     |
+------+--------------+
| Tim  | English      |
| Tom  | Math/English |
+------+--------------+

If you want the connected subjects in each group to be sorted by subject, you can use ORDER BY, as follows:

SELECT
    name AS `Name`,
    GROUP_CONCAT(subject ORDER BY subject SEPARATOR '/') AS `Subjects`
FROM student_score
GROUP BY name;

+------+--------------+
| Name | Subjects     |
+------+--------------+
| Tim  | English      |
| Tom  | English/Math |
+------+--------------+

Here, since ORDER BY subject is used, for Tom, English is ahead of Math.

If you want to concatenate subjects and scores at the same time, use the following statement:

SELECT
    name AS `Name`,
    GROUP_CONCAT(
        subject, score
        ORDER BY subject
        SEPARATOR '/'
    ) AS `Scores`
FROM student_score
GROUP BY name;

+------+------------------+
| Name | Scores           |
+------+------------------+
| Tim  | English98        |
| Tom  | English90/Math80 |
+------+------------------+

Here, we pass multiple columns into the GROUP_CONCAT() function, subject and score. Note that the output, subjects and grades are directly spliced ​​together, there is no separator.

If you want to use the separator - between subjects and grades, use the following statement:

SELECT
    name AS `Name`,
    GROUP_CONCAT(
        CONCAT(subject, '-'), score
        ORDER BY subject
        SEPARATOR '/'
    ) AS `Scores`
FROM student_score
GROUP BY name;

+------+--------------------+
| Name | Scores             |
+------+--------------------+
| Tim  | English-98         |
| Tom  | English-90/Math-80 |
+------+--------------------+

Note that we use the expression CONCAT(subject, '-') as an input parameter of the GROUP_CONCAT() function, and it is used to attach a - after the subject.

Or we use the CONCAT_WS() function for the same purpose:

SELECT
    name AS `Name`,
    GROUP_CONCAT(
        CONCAT_WS('-', subject, score)
        ORDER BY subject
        SEPARATOR '/'
    ) AS `Scores`
FROM student_score
GROUP BY name;

+------+--------------------+
| Name | Scores             |
+------+--------------------+
| Tim  | English-98         |
| Tom  | English-90/Math-80 |
+------+--------------------+