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