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: The max_length parameter only affects Vertica, as Spark-based engine utilize a STRING type that does not require length specification. In Vertica, if the result has an octet length greater than the specified max_length, the system will return 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');

-- Result in Spark-based engine: 'hello,celonis'
-- Result in Vertica: ERROR (Exceeds specified length)

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