Search

[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 

 

[ORACLE] 구분자_LIST형_맵핑형_조합형쿼리

Database 2009.11.04 18:10 Posted by 애플자라

* TB_INST_INPUT테이블에 SUBS_VALUE 데이터가 |구분자로 값이 들어오는 경우

-- SUBS_VALUE 컬럼의 데이터를 | 구분자로 짤라 각각 매핍변수로 선언

SELECT SUB1,SUB2,SUB3,SUB4,SUB5,SUB6,SUB7,SUB8,SUB9,SUB10,SUB11,SUB12,SUB13,SUB14,
SUB15
    FROM(SELECT
            MAX(DECODE(rowno,1,aa)) SUB1
           ,MAX(DECODE(rowno,2,aa)) SUB2
           ,MAX(DECODE(rowno,3,aa)) SUB3
           ,MAX(DECODE(rowno,4,aa)) SUB4
           ,MAX(DECODE(rowno,5,aa)) SUB5
           ,MAX(DECODE(rowno,6,aa)) SUB6
           ,MAX(DECODE(rowno,7,aa)) SUB7
           ,MAX(DECODE(rowno,8,aa)) SUB8
           ,MAX(DECODE(rowno,9,aa)) SUB9
           ,MAX(DECODE(rowno,10,aa)) SUB10
           ,MAX(DECODE(rowno,11,aa)) SUB11
           ,MAX(DECODE(rowno,12,aa)) SUB12
           ,MAX(DECODE(rowno,13,aa)) SUB13
           ,MAX(DECODE(rowno,14,aa)) SUB14
           ,MAX(DECODE(rowno,15,aa)) SUB15
        FROM(
             SELECT SUBSTR( RCVDATA,
                    INSTR (RCVDATA, '|', 1, LEVEL) + 1,
                    INSTR (RCVDATA, '|', 1, LEVEL + 1) -
                    INSTR (RCVDATA, '|', 1, LEVEL) - 1 ) aa,
                    ROWNUM AS rowno
               FROM (SELECT '|' || SUBS_VALUE ||'|' RCVDATA
                           ,ROWNUM
                       FROM TB_INST_INPUT
                       --WHERE INSTID = '@{EMS_M_ID}'
                       )
                    CONNECT BY LEVEL<= LENGTH(RCVDATA) -
                    LENGTH(REPLACE(RCVDATA, '|')) - 1
        )
        WHERE ROWNUM <= '15'
    )
WHERE ROWNUM = 1


-- SUBS_VALUE 컬럼의 데이터를 | 구분자로 짤라 한개의 데이터로 조합


SELECT a1||a2||a3||a4||a5||a6||a7||a8||a9||a10||a11||a12||a13||a14  totaldata
    FROM(SELECT MAX(DECODE(rowno,1,aa)) a1
           ,MAX(DECODE(rowno,2,aa)) a2
           ,MAX(DECODE(rowno,3,aa)) a3
           ,MAX(DECODE(rowno,4,aa)) a4
           ,MAX(DECODE(rowno,5,aa)) a5
           ,MAX(DECODE(rowno,6,aa)) a6
           ,MAX(DECODE(rowno,7,aa)) a7
           ,MAX(DECODE(rowno,8,aa)) A8
           ,MAX(DECODE(rowno,9,aa)) a9
           ,MAX(DECODE(rowno,10,aa)) a10
           ,MAX(DECODE(rowno,11,aa)) a11
           ,MAX(DECODE(rowno,12,aa)) a12
           ,MAX(DECODE(rowno,13,aa)) a13
           ,MAX(DECODE(rowno,14,aa)) a14
        FROM(SELECT SUBSTR( RCVDATA,
                    INSTR (RCVDATA, '|', 1, LEVEL) + 1,
                    INSTR (RCVDATA, '|', 1, LEVEL + 1) -
                    INSTR (RCVDATA, '|', 1, LEVEL) - 1 ) aa
                    ,ROWNUM AS rowno
               FROM (SELECT '|' || SUBS_VALUE || '|' RCVDATA
                           ,ROWNUM
                       FROM TB_INST_INPUT)
                    CONNECT BY LEVEL<= LENGTH(RCVDATA) -
                    LENGTH(REPLACE(RCVDATA, '|')) - 1)
    WHERE ROWNUM <= '14'
    )
   
   
-- SUBS_VALUE 컬럼의 데이터를 | 구분자로 짤라 리스트 데이터로 조합   

SELECT SUBSTR( RCVDATA,
INSTR (RCVDATA, '|', 1, LEVEL) + 1,
INSTR (RCVDATA, '|', 1, LEVEL + 1) -
INSTR (RCVDATA, '|', 1, LEVEL) - 1 ) aa
FROM (SELECT '|' || SUBS_VALUE || '|' RCVDATA FROM TB_INST_INPUT)
CONNECT BY LEVEL<= LENGTH(RCVDATA) -
LENGTH(REPLACE(RCVDATA, '|')) - 1;

참조 - 이동준형 마음ㅋ