SQL Functions and Statements
LISTAGG
(Introduced in hotfix version 9.1.1-4)
The LISTAGG function 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.
For example:
=> SELECT customer_region, listagg (DISTINCT customer_city||customer_state) AS 'City/State' FROM customer_dimension GROUP BY customer_region; customer_region | City/State -----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- NorthWest | SeattleWA,PortlandOR,BellevueWA MidWest | ChicagoIL,SpringfieldIL,Cedar RapidsIA,LansingMI,LivoniaMI,EvansvilleIN,South BendIN,MilwaukeeWI,Green BayWI,IndianapolisIN,PeoriaIL,JolietIL,NapervilleIL,ColumbusOH,GaryIN,FlintMI,DetroitMI,Ann ArborMI,Sioux FallsSD South | El PasoTX,MesquiteTX,McAllenTX,ClearwaterFL,Cape CoralFL,AthensGA,San AntonioTX,Grand PrairieTX,JacksonvilleFL,LafayetteLA,WacoTX,HoustonTX,CarrolltonTX,DallasTX,IndependenceMS,Fort WorthTX,Coral SpringsFL,BeaumontTX,AbileneTX,Wichita FallsTX,AustinTX,SavannahGA SouthWest | North Las VegasNV,TopekaKS,PeoriaAZ,Fort CollinsCO,DenverCO,PuebloCO,PhoenixAZ,WestminsterCO,GilbertAZ,ArvadaCO,Las VegasNV East | ElizabethNJ,ColumbiaSC,WashingtonDC,FayettevilleNC,HartfordCT,ManchesterNH,StamfordCT,CambridgeMA,EriePA,MemphisTN,CharlotteNC,PhiladelphiaPA,Sterling HeightsMI,PortsmouthVA,WaterburyCT,BostonMA,LowellMA,AlexandriaVA,AllentownPA,BaltimoreMD,ClarksvilleTN,New YorkNY,New HavenCT,NashvilleTN West | Rancho CucamongaCA,El MonteCA,OrangeCA,West CovinaCA,CoronaCA,Costa MesaCA,Thousand OaksCA,PasadenaCA,San DiegoCA,DowneyCA,ConcordCA,EscondidoCA,Simi ValleyCA,InglewoodCA,Daly CityCA,VallejoCA,LancasterCA,FullertonCA,SunnyvaleCA,BerkeleyCA,Los AngelesCA,San FranciscoCA,PalmdaleCA,West Valley CityUT,ProvoUT,NorwalkCA,San JoseCA,VenturaCA,Santa ClaraCA,FontanaCA,BurbankCA (6 rows)
