Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

LISTAGG Window Function

For each group in a query, the LISTAGG window function orders the rows for that group according to the ORDER BY expression, then concatenates the values into a single string.

Syntax

Copy
LISTAGG( [DISTINCT] expression [, 'delimiter' ] ) [ WITHIN GROUP (ORDER BY order_list) ] OVER ( [PARTITION BY partition_expression] )

Arguments

DISTINCT

(Optional) A clause that eliminates duplicate values from the specified expression before concatenating. Trailing spaces are ignored, so the strings 'a' and 'a ' are treated as duplicates. LISTAGG uses the first value encountered. For more information, see Significance of Trailing Blanks.

aggregate_expression

Any valid expression (such as a column name) that provides the values to aggregate. NULL values and empty strings are ignored.

delimiter

(Optional) The string constant to will separate the concatenated values. The default is NULL.

WITHIN GROUP (ORDER BY order_list)

(Optional) A clause that specifies the sort order of the aggregated values. Deterministic only if ORDER BY provides unique ordering. The default is to aggregate all rows and return a single value.

OVER

A clause that specifies the window partitioning. The OVER clause cannot contain a window ordering or window frame specification.

PARTITION BY partition_expression

(Optional) Sets the range of records for each group in the OVER clause.

Returns

VARCHAR(MAX). If the result set is larger than the maximum VARCHAR size (64K – 1, or 65535), then LISTAGG returns the following error:

Copy
Invalid operation: Result size exceeds LISTAGG limit

Examples

See LISTAGG Window Function Examples.