SQLite group_concat() Function

The SQLite group_concat() function is an aggregate function that returns a string containing all specified values in the group, separated by commas.

Syntax

Here is the syntax of the SQLite group_concat() function:

group_concat(expr)

Parameters

expr: Required. A column name or expression.

Return value

The SQLite group_concat() function returns a comma-separated string containing all the values in the specified expression or column in a group.

Examples

Here are some examples to show the usages of group_concat().

Basic usage

This example illustrates the basic usage of the SQLite group_concat() function.

SELECT group_concat('Hello');

group_concat('Hello')
---------------------
Hello

This example is meaningless. Generally, the SQLite group_concat() function is used in aggregate queries on tables.

Use `group_concat()` return

To demonstrate the usages of group_concat(), we simulate a temporary set with the following UNION statement:

SELECT 'Tim' name, 'Football' hobby
UNION
SELECT 'Tim' name, 'Baseball' hobby
UNION
SELECT 'Tom' name, 'Piano' hobby
UNION
SELECT 'Tom' name, 'violin' hobby;

name  hobby
----  --------
Tim   Baseball
Tim   Football
Tom   Piano
Tom   violin

Here, we have some rows about user hobbies. Among them, each row is a hobby of a user.

To concatenate all hobbies of each user into a text value, use the following statement:

SELECT
    t.name,
    group_concat(t.hobby) hobbies
FROM (
    SELECT 'Tim' name, 'Football' hobby
    UNION
    SELECT 'Tim' name, 'Baseball' hobby
    UNION
    SELECT 'Tom' name, 'Piano' hobby
    UNION
    SELECT 'Tom' name, 'violin' hobby
) t
GROUP BY t.name;

name  hobbies
----  -----------------
Tim   Baseball,Football
Tom   Piano,violin

According the statement, SQLite divides all rows into two groups by name first, and concatenate all hobbies as a text value specified by commas in each group.