Geeks on Campus

anything, everything, geeky.

LISTAGG Examples

Posted by jeggent on December 9, 2020

I have a post on PS Query Expressions and I was about to add a LISTAGG example to it. But I thought that instead I would create a new post just for LISTAGG examples since there may end up being many of them. The post Combine Multiple Rows and Fields explains LISTAGG.

FA award short description, status, and amount:
LISTAGG(CONCAT(C.DESCRSHORT, CONCAT(‘ (‘,CONCAT(DECODE(B.AWARD_STATUS,’C’,’Cancelled’,’D’,’Declined’,’Accepted’), CONCAT(‘) ‘,B.DISBURSED_AMOUNT)))), ‘ – ‘) WITHIN GROUP (ORDER BY B.ITEM_TYPE)

Output:
SubLn (Accepted) 1732 – UnsubLn (Accepted) 990 – UnsubLn8 (Accepted) 1979 – Plus Ln (Cancelled) 0

Academic plan description and code:
LISTAGG(CONCAT(B.DESCR,CONCAT(‘ (‘, CONCAT(B.ACAD_PLAN, ‘) ‘))), ‘ / ‘)

Output:
Electromechanical Technology (ET-AASEEET) / Electr-Mechanical Technology (EMECT-AAS)

FA Load by Term:
LISTAGG(CONCAT(G.STRM,CONCAT(‘ (‘,CONCAT(G.FA_LOAD,’)’))), ‘ / ‘) WITHIN GROUP (ORDER BY G.STRM)

Output:
2205 (L) / 2208 (F)