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
orDESC
- returns the values in ascending or descending order, respectively. The default order is ascending.
Examples
LISTAGG exampleSELECT LISTAGG(city, '; ')
FROM "Samples"."samples.dremio.com"."zips.json"
-- AGAWAM; CUSHMAN; BARRE; BELCHERTOWN; BLANDFORD; BRIMFIELD; CHESTER; CHESTERFIELD; CHICOPEE; CHICOPEE
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
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.