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: Keeps duplicate values in the return list. This is the default behavior.

• DISTINCT: Removes duplicate values from the return list.

• measure_expr: A string column or value.

• delimiter: 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] )]: 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 eth.recent_blocks

-- AGAWAM; CUSHMAN; BARRE; BELCHERTOWN; BLANDFORD; BRIMFIELD; CHESTER; CHESTERFIELD; CHICOPEE; CHICOPEE

LISTAGG example

SELECT LISTAGG(city, ', ') 
WITHIN GROUP (ORDER BY city DESC) "city_list" 
FROM eth.recent_blocks

-- 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 eth.recent_blocks
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 Spice truncates it.