Skip to main content

LISTAGG

Categories: Aggregate

LISTAGG

Concatenates a group of rows into a list of strings and places a separator between them.

Syntax

LISTAGG ( [ALL | DISTINCT] measure_expr [, 'delimiter'] ) [WITHIN GROUP ( ORDER BY measure_expr [ASC | DESC] )]

  • ALL (optional): Keeps duplicate values in the return list. This is the default behavior.
  • DISTINCT (optional): Removes duplicate values from the return list.
  • measure_expr: A string column or value.
  • delimiter (optional): Designates a string literal to separate the measure column values. If a delimiter is not specified, will default to NULL.
  • [WITHIN GROUP ( ORDER BY measure_expr [ASC | DESC] )] (optional): Determines the order in which the concatenated values are returned. Using one of the optional keywords - ASC or DESC - returns the values in ascending or descending order, respectively. The default order is ascending.

Examples

LISTAGG example
SELECT LISTAGG(city, '; ')
FROM "Samples"."samples.dremio.com"."zips.json"
-- AGAWAM; CUSHMAN; BARRE; BELCHERTOWN; BLANDFORD; BRIMFIELD; CHESTER; CHESTERFIELD; CHICOPEE; CHICOPEE
LISTAGG example
SELECT LISTAGG(city, ', ') 
WITHIN GROUP (ORDER BY city DESC) "city_list"
FROM Samples."samples.dremio.com"."zips.json"
-- city_list
-- ZWOLLE; ZWINGLE; ZURICH; ZUNI; ZUNI; ZUMBROTA; ZORTMAN; ZOLFO SPRINGS; ZOE; ZOARVILLE; ZIRCONIA; ZIO
LISTAGG example
SELECT state,
LISTAGG(DISTINCT city, '| ')
WITHIN GROUP (ORDER BY city) "city_list"
FROM Samples."samples.dremio.com"."zips.json"
GROUP BY state
ORDER BY state DESC
-- state, city_list
-- WY, 82057| ACME| AFTON| ALADDIN| ALBIN| ALCOVA| ARAPAHOE| ARMINTO| ARVADA| AUBURN| BAGGS| BAIROIL| BANNE
-- WV, 24939| 25242| 25536| ABRAHAM| ADRIAN| ADVENT| ALBRIGHT| ALEXANDER| ALGOMA| ALKOL| ALUM BRIDGE| ALUM
-- WI, ABBOTSFORD| ABRAMS| ADAMS| ADELL| ALBANY| ALGOMA| ALLENTON| ALLOUEZ| ALMA| ALMA CENTER| ALMENA| ALMO

Usage Notes

If the return list is greater than 32768 bytes, then Dremio truncates it.