listagg to separate lists depending on other column

26 Views Asked by At

How do I LISTAGG values to 2 separate list depending on value in another column? This format create 2 separate lines

    SELECT 
       PO.COMMNUM,
       CASE 
       WHEN  SOP.PORT_INSTD_OPT = 'N'
       THEN LISTAGG(PO.OPT, ',') WITHIN GROUP (ORDER BY PO.OPT)
       END AS FACTORY,
       CASE 
       WHEN  SOP.PORT_INSTD_OPT = 'Y'
       THEN  LISTAGG(PO.OPT, ',') WITHIN GROUP (ORDER BY PO.OPT)  
       END AS MDO
       FROM PARSEOPTS PO
       JOIN PIPELINE PL ON PL.COMMNUM = PO.COMMNUM
       JOIN CP_DBA.SOP_SALE_OPT SOP ON PO.MDL_YR = SOP.MDL_YR 
       AND PO.OPT = SOP.SALE_OPT_CDE 
       AND SOP.CNTY_SALES_PGM_CDE = 'X9A' 
       AND SOP.VEH_MAKE_CDE = 'VLK'
       GROUP BY PO.COMMNUM, SOP.PORT_INSTD_OPT
       ORDER BY PO.COMMNUM)

But I Get errors when trying 1 case statement with 2 LISTAGG

0

There are 0 best solutions below