如何将拆分分组后的SQL查询结果合并为单条记录？

背景信息 #

原始数据 #

解析后的数据 #

当前执行的SQL #

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;

当前查询结果 #

期望结果 #

最优解决方案 #

调整CASE与LISTAGG的嵌套顺序，同时移除分组中的SOP.PORT_INSTD_OPT，即可将同COMMNUM的结果合并为单条：

SELECT PO.COMMNUM,
       LISTAGG(CASE WHEN SOP.PORT_INSTD_OPT = 'N' THEN PO.OPT END, ',') 
           WITHIN GROUP (ORDER BY PO.OPT) AS FACTORY,
       LISTAGG(CASE WHEN SOP.PORT_INSTD_OPT = 'Y' THEN PO.OPT END, ',') 
           WITHIN GROUP (ORDER BY PO.OPT) 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
ORDER BY PO.COMMNUM;

原理说明 #

• 先通过CASE筛选出对应标识的OPT值，再对筛选结果执行LISTAGG聚合，避免按标识字段分组导致的多行结果。
• 仅按COMMNUM分组，确保同一COMMNUM下的所有记录聚合到同一行。
• LISTAGG会自动忽略CASE返回的NULL值，不会生成多余的分隔符。

内容的提问来源于stack exchange，提问作者Kristine