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

LISTAGG Function

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

Syntax

Copy
LISTAGG(aggregate_expression [, 'delimiter' ] ) [ WITHIN GROUP (ORDER BY order_list) ]

Arguments

aggregate_expression

Any valid expression (such as a column name) that provides the values to aggregate. NULL values and empty strings are ignored. LISTAGG does not support DISTINCT expressions.

delimiter

Optional. The string constant that will separate the concatenated values. The default is NULL.

WITHIN GROUP (ORDER BY order_list)

Optional. Specifies the sort order of the aggregated values.

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

Usage Notes

If a statement includes multiple LISTAGG functions that use WITHIN GROUP clauses, each WITHIN GROUP clause must use the same ORDER BY values.

For example, the following statement will return an error.

Copy
select listagg(sellerid) within group (order by dateid) as sellers, listagg(dateid) within group (order by sellerid) as dates from winsales;

The following statements will execute successfully.

Copy
select listagg(sellerid) within group (order by dateid) as sellers, listagg(dateid) within group (order by dateid) as dates from winsales; select listagg(sellerid) within group (order by dateid) as sellers, listagg(dateid) as dates from winsales;

Examples

The following example aggregates seller IDs, ordered by seller ID.

Copy
select listagg(sellerid) within group (order by sellerid) from winsales; listagg ------------ 11122333344

The following example aggregates seller IDs in date order.

Copy
select listagg(sellerid) within group (order by dateid) from winsales; listagg ------------- 31141242333

The following example returns a pipe-separated list of sales dates for buyer B.

Copy
select listagg(dateid,'|') within group (order by sellerid desc,salesid asc) from winsales where buyerid = 'b'; listagg --------------------------------------- 2003-08-02|2004-04-18|2004-04-18|2004-02-12

The following example returns a comma-separated list of sales IDs for each buyer ID.

Copy
select buyerid, listagg(salesid,',') within group (order by salesid) as sales_id from winsales group by buyerid order by buyerid; buyerid | sales_id -----------+------------------------ a |10005,40001,40005 b |20001,30001,30004,30003 c |10001,20002,30007,10006