[ORACLE]WMSYS.WM_CONCAT 월별데이터 합치기

SELECT 
    GROUP_NAME,
    REPLACE(REPLACE(WMSYS.WM_CONCAT(DISTINCT MARKETING_DATE_01),',','<br/>'),'(Meeting)','') AS DATE_01,

    REPLACE(REPLACE(WMSYS.WM_CONCAT(DISTINCT MARKETING_DATE_02),',','<br/>'),'(Meeting)','') AS DATE_02,

    REPLACE(REPLACE(WMSYS.WM_CONCAT(DISTINCT MARKETING_DATE_03),',','<br/>'),'(Meeting)','') AS DATE_03,

    REPLACE(REPLACE(WMSYS.WM_CONCAT(DISTINCT MARKETING_DATE_04),',','<br/>'),'(Meeting)','') AS DATE_04,

    REPLACE(REPLACE(WMSYS.WM_CONCAT(DISTINCT MARKETING_DATE_05),',','<br/>'),'(Meeting)','') AS DATE_05,

    REPLACE(REPLACE(WMSYS.WM_CONCAT(DISTINCT MARKETING_DATE_06),',','<br/>'),'(Meeting)','') AS DATE_06,

    REPLACE(REPLACE(WMSYS.WM_CONCAT(DISTINCT MARKETING_DATE_07),',','<br/>'),'(Meeting)','') AS DATE_07,

    REPLACE(REPLACE(WMSYS.WM_CONCAT(DISTINCT MARKETING_DATE_08),',','<br/>'),'(Meeting)','') AS DATE_08,

    REPLACE(REPLACE(WMSYS.WM_CONCAT(DISTINCT MARKETING_DATE_09),',','<br/>'),'(Meeting)','') AS DATE_09,

    REPLACE(REPLACE(WMSYS.WM_CONCAT(DISTINCT MARKETING_DATE_10),',','<br/>'),'(Meeting)','') AS DATE_10,

    REPLACE(REPLACE(WMSYS.WM_CONCAT(DISTINCT MARKETING_DATE_11),',','<br/>'),'(Meeting)','') AS DATE_11,

    REPLACE(REPLACE(WMSYS.WM_CONCAT(DISTINCT MARKETING_DATE_12),',','<br/>'),'(Meeting)','') AS DATE_12

FROM (SELECT GROUP_NAME,
       MARKETING_DATE,
       CASE WHEN SUBSTR (MARKETING_DATE, 6, 2) = '01' THEN MARKETING_DATE END MARKETING_DATE_01,
       CASE WHEN SUBSTR (MARKETING_DATE, 6, 2) = '02' THEN MARKETING_DATE END MARKETING_DATE_02,
       CASE WHEN SUBSTR (MARKETING_DATE, 6, 2) = '03' THEN MARKETING_DATE END MARKETING_DATE_03,
       CASE WHEN SUBSTR (MARKETING_DATE, 6, 2) = '04' THEN MARKETING_DATE END MARKETING_DATE_04,
       CASE WHEN SUBSTR (MARKETING_DATE, 6, 2) = '05' THEN MARKETING_DATE END MARKETING_DATE_05,
       CASE WHEN SUBSTR (MARKETING_DATE, 6, 2) = '06' THEN MARKETING_DATE END MARKETING_DATE_06,
       CASE WHEN SUBSTR (MARKETING_DATE, 6, 2) = '07' THEN MARKETING_DATE END MARKETING_DATE_07,
       CASE WHEN SUBSTR (MARKETING_DATE, 6, 2) = '08' THEN MARKETING_DATE END MARKETING_DATE_08,
       CASE WHEN SUBSTR (MARKETING_DATE, 6, 2) = '09' THEN MARKETING_DATE END MARKETING_DATE_09,
       CASE WHEN SUBSTR (MARKETING_DATE, 6, 2) = '10' THEN MARKETING_DATE END MARKETING_DATE_10,
       CASE WHEN SUBSTR (MARKETING_DATE, 6, 2) = '11' THEN MARKETING_DATE END MARKETING_DATE_11,
       CASE WHEN SUBSTR (MARKETING_DATE, 6, 2) = '12' THEN MARKETING_DATE END MARKETING_DATE_12,
       ROW_NUMBER () OVER (PARTITION BY GROUP_NAME ORDER BY MARKETING_DATE) RNUM
  FROM TB_MARKETING
 WHERE     SUBSTR (MARKETING_DATE, 0, 4) = '2018'
       --AND GROUP_NAME = '건설기술용역공제조합' 
     )
START WITH RNUM = 1
CONNECT BY PRIOR RNUM = RNUM - 1 AND PRIOR GROUP_NAME = GROUP_NAME
GROUP BY GROUP_NAME
ORDER BY GROUP_NAME ASC

 

출처 - http://blog.naver.com/PostView.nhn?blogId=jaejin1028&logNo=220793468335