On this page

    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: 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

    SELECT 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.