Vertica Analytics Platform Version 9.3.x Documentation


Transforms non-null values from a group of rows into a list of values that are delimited by a configurable separator. LISTAGG can be used to denormalize rows into a string of comma-separated values or other human-readable formats.

Behavior Type



LISTAGG ( [ DISTINCT ] column‑expression 
          [ USING PARAMETERS [max_length=integer-expr] 
                             [, separator='char'] 
                             [, on_overflow={'ERROR'|'TRUNCATE'}] ] )



A table column or column expression to select from the source table or view.

LISTAGG does not support spatial data types directly. In order to pass column data of this type, convert the data to strings with the geospatial function ST_AsText.

Converted spatial data frequently contains commas. LISTAGG uses comma as the default separator character. To avoid ambiguous output, override this default by setting the function's separator parameter to another character.

Parameter Settings

Parameter name Set to…

An integer or integer expression that specifies in bytes the maximum length of the result, up to 32M.

Default: 1024


Separator character.

Default: Comma (,)


Specifies behavior when the result overflows the max_length setting, one of the following strings:

  • ERROR (default): Return an error when overflow occurs.
  • TRUNCATE: Remove any characters that exceed max_length setting from the query result, and return the truncated string.




=> SELECT customer_region Region, 
   LISTAGG (DISTINCT customer_city||customer_state USING PARAMETERS max_length=80, on_overflow='TRUNCATE') CityState
   FROM customer_dimension GROUP BY Region;
  Region   |                                    CityState
 West      | Simi ValleyCA,InglewoodCA,Daly CityCA,VallejoCA,LancasterCA,FullertonCA,Sunnyval
 NorthWest | BellevueWA,PortlandOR,SeattleWA
 MidWest   | IndianapolisIN,PeoriaIL,JolietIL,NapervilleIL,South BendIN,MilwaukeeWI,Green Bay
 South     | ClearwaterFL,Cape CoralFL,El PasoTX,MesquiteTX,McAllenTX,AthensGA,San AntonioTX,
 SouthWest | TopekaKS,PeoriaAZ,Fort CollinsCO,DenverCO,PuebloCO,PhoenixAZ,WestminsterCO,North
 East      | ManchesterNH,StamfordCT,CambridgeMA,EriePA,ElizabethNJ,ColumbiaSC,WashingtonDC,F
(6 rows)