Search

'2018/04'에 해당되는 글 1건

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

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

Database 2018.04.06 09:17 Posted by 애플자라

SELECT 
    GROUP_NAME,
    WMSYS.WM_CONCAT(MARKETING_DATE_01) AS DATE_01,
    WMSYS.WM_CONCAT(MARKETING_DATE_02) AS DATE_02,
    WMSYS.WM_CONCAT(MARKETING_DATE_03) AS DATE_03,
    WMSYS.WM_CONCAT(MARKETING_DATE_04) AS DATE_04,
    WMSYS.WM_CONCAT(MARKETING_DATE_05) AS DATE_05,
    WMSYS.WM_CONCAT(MARKETING_DATE_06) AS DATE_06,
    WMSYS.WM_CONCAT(MARKETING_DATE_07) AS DATE_07,
    WMSYS.WM_CONCAT(MARKETING_DATE_08) AS DATE_08,
    WMSYS.WM_CONCAT(MARKETING_DATE_09) AS DATE_09,
    WMSYS.WM_CONCAT(MARKETING_DATE_10) AS DATE_10,
    WMSYS.WM_CONCAT(MARKETING_DATE_11) AS DATE_11,
    WMSYS.WM_CONCAT(MARKETING_DATE_12) 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