LISTAGG
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
Syntax
LISTAGG ( [ DISTINCT ] column‑expression [ USING PARAMETERS [max_length=integer-expr] [, separator='char'] [, on_overflow={'ERROR'|'TRUNCATE'}] ] )
Arguments
column‑expression |
A table column or column expression to select from the source table or view.
Converted spatial data frequently contains commas. |
Parameter Settings
Parameter name | Set to… |
---|---|
max_length
|
An integer or integer expression that specifies in bytes the maximum length of the result, up to 32M. Default: 1024 |
separator
|
Separator character. Default: Comma ( |
on_overflow
|
Specifies behavior when the result overflows the
|
Privileges
None
Examples
=> 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)