LISTAGG

SQLstream includes support for a LISTAGG ordered set aggregate function. This function orders the string values within each group as defined in ORDER BY clause and then returns the concatenated values each separated by a delimiter. The return type of LISTAGG is VARCHAR with defined length of 4096 or the sizeof <character value expression>, whichever of them is greater.

Syntax

<listagg set function> ::=
LISTAGG <left paren> [ <set quantifier> ] <character value expression>
<comma> <listagg separator> <right paren>

The values are always sorted based on the ROWTIME. The function removes all the null values and performs the ordering before the aggregation. If no NOT NULL value remains, LISTAGG function returns the NULL value.

The LISTAGG function also supports the duplicate elimination of the values by using the DISTINCT keyword as follows:

Without separator:

SELECT STREAM ROWTIME, LISTAGG(DISTINCT CAST(FEATURE AS VARCHAR(10000)) FROM S GROUP BY S.ROWTIME;

With separator:

SELECT STREAM ROWTIME, LISTAGG(CAST(FEATURE AS VARCHAR(10000), ',') FROM S GROUP BY S.ROWTIME;

Example

SELECT STREAM ROWTIME, LISTAGG(CAST(FEATURE AS VARCHAR(10000))
FROM S
GROUP BY S.ROWTIME;