MariaDB GROUP_CONCAT() Function

In MariaDB, GROUP_CONCAT() is a built-in function that concatenates the values ​​of columns or expressions specified in a group into a string and returns it.

MariaDB GROUP_CONCAT() Syntax

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

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

We generally use the GROUP_CONCAT() function in the following SQL statement:

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 content to be concatenated.

SEPARATOR separator

Optional. The separator. The default is ,.

Return value

The MariaDB GROUP_CONCAT(expr) function returns a string that concatenates all values specified by columns or expressions.

This function will return NULL if the result set does not have any rows.

MariaDB GROUP_CONCAT() Examples

We’ll demonstrate MariaDB GROUP_CONCAT() function 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 create a table to store the grades of students, this table consists of 4 columns:

• id is the primary key
• name is the student’s first name
• subject is the student’s subject
• score is the student’s grade

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 all the rows in the table:

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

Now, we can use the GROUP_CONCAT() function to get a list of the subjects for each person, 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 first group by all rows by name and use the GROUP_CONCAT() function to concatenate the contents of the subject columns.

In the above statement, since no delimiter is specified, a comma is used , for concatenation. If we want to use other delimiters (for example: /), pass it to the SEPARATOR parameter:

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 name, you can use the ORDER BY clause 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, due to the setting ORDER BY subject, for Tom, English ranks in front of Math.

If we want to concatenate subjects and grades at the same time, please 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 in the GROUP_CONCAT() function, subject and score. Pay attention to the output, the subjects and grades are directly stitched together, there is no connector in the middle.

If we want to use a connector (for example -) 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 have used the CONCAT(subject, '-') expression here as an input parameter of the GROUP_CONCAT() function.

Or we use the CONCAT_WS() function to achieve 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 |
+------+--------------------+

Conclusion

In MariaDB, GROUP_CONCAT() is a built-in function that concatenates the values ​​of columns or expressions specified in a group into a string and returns it.