SQLite | SQLite Functions | Concatenating Column Values (group_concat Function)

Tags:

• SQLite
• Database

The group_concat function joins values from a specified column into a string separated by a delimiter. This article explains how to use it.

Using the group_concat Function

Use the following syntax.

group_concat(column_name)

The function concatenates all non-NULL values in the specified column. With a GROUP BY clause, it produces a separate result for each group.

By default, values are separated with commas. Supply a second argument to use another delimiter.

group_concat(column_name, delimiter)

–

Create a table for the example.

create table user (id integer, name text, gender text);

sqlite> create table user (id integer, name text, gender text);
sqlite> 

Insert the following data.

insert into user values (1, 'devkuma', 'Man');
insert into user values (2, 'kimkc', 'Woman');
insert into user values (3, 'araikuma', 'Man');
insert into user values (4, NULL, 'Man');
insert into user values (5, 'raccoon', 'Woman');

sqlite> insert into user values (1, 'devkuma', 'Man');
sqlite> insert into user values (2, 'kimkc', 'Woman');
sqlite> insert into user values (3, 'araikuma', 'Man');
sqlite> insert into user values (4, NULL, 'Man');
sqlite> insert into user values (5, 'raccoon', 'Woman');
sqlite> 

Concatenate the values in the name column.

select group_concat(name) from user;

sqlite> select group_concat(name) from user;
group_concat(name)            
------------------------------
devkuma,kimkc,araikuma,raccoon
sqlite> 

The non-NULL names are joined with the default comma delimiter.

Next, group rows by gender and concatenate the names in each group.

select gender, group_concat (name) from user group by gender;

sqlite> select gender, group_concat (name) from user group by gender;
gender      group_concat (name)
----------  -------------------
Man         devkuma,araikuma   
Woman       kimkc,raccoon      
sqlite> 

With GROUP BY, values are concatenated separately for each group. See Calculating Sums and Averages by Group (GROUP BY and HAVING) for details.

Specifying a Delimiter

To replace the default comma, pass the delimiter as the second argument to group_concat.

The following query joins names with a plus sign (+).

select group_concat(name, '+') from user;

sqlite> select group_concat(name, '+') from user;
group_concat(name, '+')       
------------------------------
devkuma+kimkc+araikuma+raccoon
sqlite> 

The names are displayed as one string separated by the specified plus sign.