Aggregates Aggregate

listagg()

Concatenates the column string values with an optional separator.

Alias of string_agg()

Arguments & returns

2 overloads

`listagg(str)`

Aggregate

Overload link	`str`	Returns
#01	`ANY`	`VARCHAR`

`listagg(str, arg)`

Aggregate

Overload link	`str`	`arg`	Returns
#02	`ANY`	`VARCHAR`	`VARCHAR`

Example

SELECT listagg(A, '-')
FROM (VALUES
  (10),
  (20),
  (NULL)
) AS sample("A");

Haybarn WASM 1.5.5-rc3 · In your browser

Engine example with sample data · Run to view results.

In other engines

Amazon Redshift`listagg()`

Same function

ClickHouse`groupconcat()`

SELECT groupConcat(', ')(part_name) FROM t

In Haybarn

SELECT LISTAGG(part_name, ', ') FROM t

SQLGlot example ↗

Used in larger rewrites 1

These examples use listagg as one part of a larger SQL translation.

Snowflake`randstr()`

SELECT RANDSTR(10, 123)

In Haybarn

SELECT (SELECT LISTAGG(SUBSTRING('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', 1 + CAST(FLOOR(random_value * 62) AS INT), 1), '') FROM (SELECT (ABS(HASH(i + 123)) % 1000) / 1000.0 AS random_value FROM RANGE(10) AS t(i)))

SQLGlot example ↗

Available in Haybarn 1.5 DuckDB 1.5 DuckDB 1.4 DuckDB 1.3

Arguments & returns

listagg(str)

listagg(str, arg)