STRING_AGG

Reference material for STRING_AGG function

Concatenates non-NULL string values from a group into a single string, with each value separated by the specified delimiter.

Syntax

STRING_AGG(<expression>, <delimiter>)

Parameters

Parameter	Description	Supported input types
<expression>	The string expression to aggregate.	TEXT
<delimiter>	The string placed between each pair of concatenated values.	TEXT

Return Type

TEXT

Remarks

• NULL values in <expression> are silently skipped.
• If all input values are NULL, or if the input set is empty, STRING_AGG returns NULL.
• No specific order for the concatenation of values is guaranteed. To concatenate in a specific order, use ARRAY_AGG, ARRAY_SORT, and ARRAY_TO_STRING.
• An empty <delimiter> (empty string '') concatenates values without any separator.

Example

The levels table contains the following values:

SELECT level, name, leveltype FROM levels ORDER BY level;

level int null	name text null	leveltype text null
1	Thunderbolt Circuit	FastestLap
2	Velocity Vale	FirstToComplete
3	Raceway Ridge	FastestLap
4	Nitro Narrows	FirstToComplete
5	Thunder Road	FirstToComplete
6	Burnout Boulevard	Drift
7	Speed Street	FastestLap
8	Racing Ravine	FastestLap
9	Drift District	Drift
10	Acceleration Alley	FirstToComplete

Rows: 10Execution time: 5.13ms

The following example concatenates all level names within each leveltype, separated by commas:

SELECT leveltype, STRING_AGG(name, ', ') AS levels
FROM levels
GROUP BY leveltype
ORDER BY leveltype;

leveltype text null	levels text null
Drift	Burnout Boulevard, Drift District
FastestLap	Thunderbolt Circuit, Raceway Ridge, Speed Street, Racing Ravine
FirstToComplete	Velocity Vale, Nitro Narrows, Thunder Road, Acceleration Alley

Rows: 3Execution time: 10.18ms

The following example shows that NULL values are skipped:

SELECT STRING_AGG(a, ',') AS result
FROM (VALUES ('a'), (NULL), ('b'), ('c')) AS t(a);

result text null
a,b,c

Rows: 1Execution time: 3.82ms