group_concat and string_agg ​

Calculates a string from the non-NULL values in a group, separated by a delimiter.

group_concat uses a comma when the delimiter is omitted. string_agg is the two-argument alias and requires an explicit delimiter.

Syntax

group_concat(value [, separator] [ORDER BY expression])
string_agg(value, separator [ORDER BY expression])

Arguments

• value — The value to concatenate. Non-text values are converted to their text representation. NULL values are skipped.
• separator — The text placed between contributing values. The default for group_concat is ','. A NULL separator adds no bytes.

When the separator is an expression that varies by row, each contributing row after the first uses its own separator. No separator is written before the first value.

Returned value

A text string. Empty and all-NULL inputs return NULL. A contributing empty string produces an empty string, rather than NULL.

Examples

Joining employee names

CREATE TABLE employees (name TEXT);
INSERT INTO employees VALUES ('Grace'), ('Ada'), (NULL), ('Linus');

SELECT group_concat(name, ', ' ORDER BY name) AS names
FROM employees
FORMAT csv;

"Ada, Grace, Linus"

Using the default separator and an explicit separator

CREATE TABLE items (value TEXT);
INSERT INTO items VALUES ('b'), ('a'), ('c');

SELECT group_concat(value ORDER BY value),
       string_agg(value, ' | ' ORDER BY value)
FROM items
FORMAT csv;

"a,b,c","a | b | c"

Removing duplicate values

CREATE TABLE tags (value TEXT);
INSERT INTO tags VALUES ('sql'), ('rust'), ('sql');

SELECT group_concat(DISTINCT value ORDER BY value)
FROM tags
FORMAT csv;

"rust,sql"