Skip to main content

LISTAGG aggregate function

Returns concatenated VARCHAR values within a group, separated by a separator.

Please note that LISTAGG is not a supported window function in CeloSQL.

Syntax

LISTAGG( [DISTINCT] expr [, separator [, max_length]] )

Arguments

  • expr: An expression that evaluates to a VARCHAR.

  • separator: Optional. A constant of type VARCHAR that is used to separate the concatenated values. The default value is ,

  • max_length: Optional. A constant of type BIGINT that specifies the maximum length of the result in bytes. The default is 1024 and this value can not exceed 65000.

Note: max_length parameter will only affect Vertica VARCHAR as Databricks STRING does not allow for specifiying lengths. On Vertica, if the result has an octet length greater than max_length, Vertica will throw an error.

Returns

Returns a VARCHAR.

Examples

> SELECT LISTAGG(x) FROM (SELECT 'hello' as x UNION ALL SELECT 'celonis');
'hello,celonis'

> SELECT LISTAGG(x, ':)') FROM (SELECT 'hello' as x UNION ALL SELECT 'celonis');
'hello:)celonis'

> SELECT LISTAGG(x, ',', 1) FROM (SELECT 'hello' as x UNION ALL SELECT 'celonis');
Vertica: ERROR
Databricks: 'hello,celonis'

> SELECT LISTAGG(x, ',', 7) FROM (SELECT '123' as x UNION ALL SELECT '456');
'123,456'