MariaDB GROUP_CONCAT() Function: Syntax and Examples

GROUP_CONCAT() is a MariaDB aggregate function that combines non-NULL values from a group into one string. This guide covers ordering, duplicate removal, separators, and the MariaDB-specific LIMIT clause. See the MariaDB GROUP_CONCAT() reference for the full syntax.

How GROUP_CONCAT() Works

The MariaDB GROUP_CONCAT() function is used to concatenate values from rows into a single string, grouped by a specified column or expression. It is particularly useful when you need to combine multiple rows into a single comma-separated string, enabling you to represent a set of values in a single row.

Syntax

The syntax for the MariaDB GROUP_CONCAT() function is as follows:

GROUP_CONCAT([DISTINCT] expr [, expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [, col_name ...]]
             [SEPARATOR str_val]
             [LIMIT {[offset,] row_count | row_count OFFSET offset}])

• DISTINCT: An optional keyword that removes duplicate values from the result.
• expr: The expression(s) to be concatenated. Multiple expressions can be specified, separated by commas.
• ORDER BY: An optional clause that specifies the order in which the values should be concatenated. You can order by an unsigned integer, column name, or expression, followed by ASC (ascending) or DESC (descending) order. Multiple columns can be specified for sorting.
• SEPARATOR: An optional string value that separates the concatenated values. If omitted, the default separator is a comma (,).
• LIMIT: An optional MariaDB clause that limits the number of concatenated values, with an optional offset.

The function returns a string, or NULL when the group has no non-NULL values. The result can be truncated at the group_concat_max_len system variable; MariaDB documents a default maximum of 1M bytes. When that limit is 512 bytes or less, the result type is VARCHAR or VARBINARY; otherwise it is TEXT or BLOB.

Examples

Example 1: Basic Usage

This example demonstrates the basic usage of the GROUP_CONCAT() function.

DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

INSERT INTO employees (id, name)
VALUES
    (1, 'John'),
    (2, 'Jane'),
    (3, 'Alice'),
    (4, 'Bob'),
    (5, 'Charlie');

SELECT GROUP_CONCAT(name) FROM employees;

The output will be:

+-----------------------------+
| GROUP_CONCAT(name)          |
+-----------------------------+
| John,Jane,Alice,Bob,Charlie |
+-----------------------------+

In this example, the GROUP_CONCAT() function concatenates all the names from the employees table into a single string, separated by commas.

Example 2: Using DISTINCT

This example shows how to use the DISTINCT keyword to remove duplicate values.

DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

INSERT INTO employees (id, name)
VALUES
    (1, 'John'),
    (2, 'Jane'),
    (3, 'Alice'),
    (4, 'Bob'),
    (5, 'Charlie'),
    (6, 'John');

SELECT GROUP_CONCAT(DISTINCT name) FROM employees;

The output will be:

+-----------------------------+
| GROUP_CONCAT(DISTINCT name) |
+-----------------------------+
| Alice,Bob,Charlie,Jane,John |
+-----------------------------+

If there are duplicate names in the employees table, the DISTINCT keyword will ensure that each name appears only once in the concatenated string.

Example 3: Ordering the Concatenated Values

This example demonstrates how to order the concatenated values using the ORDER BY clause.

DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

INSERT INTO employees (id, name)
VALUES
    (1, 'John'),
    (2, 'Jane'),
    (3, 'Alice'),
    (4, 'Bob'),
    (5, 'Charlie');

SELECT GROUP_CONCAT(name ORDER BY name DESC) FROM employees;

The output will be:

+---------------------------------------+
| GROUP_CONCAT(name ORDER BY name DESC) |
+---------------------------------------+
| John,Jane,Charlie,Bob,Alice           |
+---------------------------------------+

In this example, the names are concatenated in descending alphabetical order.

Example 4: Using a Custom Separator

This example shows how to use a custom separator instead of the default comma.

DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

INSERT INTO employees (id, name)
VALUES
    (1, 'John'),
    (2, 'Jane'),
    (3, 'Alice'),
    (4, 'Bob'),
    (5, 'Charlie');

SELECT GROUP_CONCAT(name SEPARATOR ' | ') FROM employees;

The output will be:

+-------------------------------------+
| GROUP_CONCAT(name SEPARATOR ' | ')  |
+-------------------------------------+
| John | Jane | Alice | Bob | Charlie |
+-------------------------------------+

In this example, the names are concatenated and separated by the | character.

Example 5: Concatenating Multiple Expressions

This example demonstrates how to concatenate multiple expressions using the GROUP_CONCAT() function.

DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50)
);

INSERT INTO employees (id, name, department)
VALUES
    (1, 'John', 'Sales'),
    (2, 'Jane', 'Marketing'),
    (3, 'Alice', 'IT'),
    (4, 'Bob', 'Sales'),
    (5, 'Charlie', 'IT');

SELECT
    GROUP_CONCAT(name, ' (', department, ')') AS employee_info
FROM
    employees;

The output will be:

+-------------------------------------------------------------------+
| employee_info                                                     |
+-------------------------------------------------------------------+
| John (Sales),Jane (Marketing),Alice (IT),Bob (Sales),Charlie (IT) |
+-------------------------------------------------------------------+

In this example, the GROUP_CONCAT() function concatenates the name and department columns, separated by a space and parentheses.

Example 6: Limit the Concatenated Values

Use LIMIT inside GROUP_CONCAT() to include only the first three names after ordering by id:

SELECT GROUP_CONCAT(name ORDER BY id LIMIT 3) AS first_three_names
FROM employees;

The result is John,Jane,Alice.

Related Functions

The following are a few functions related to the MariaDB GROUP_CONCAT() function:

• MariaDB CONCAT() function is used to concatenate two or more strings into a single string.
• MariaDB CONCAT_WS() function is used to concatenate strings with a separator.

Conclusion

The MariaDB GROUP_CONCAT() function combines values from multiple rows into one string. Use its ORDER BY, DISTINCT, SEPARATOR, and LIMIT clauses to control the output, and check group_concat_max_len when results are unexpectedly truncated.