DBILITY

oracle unpivot column to row (열을 행으로) 변환하기 본문

database/sql

oracle unpivot column to row (열을 행으로) 변환하기

DBILITY 2016. 10. 12. 20:59
반응형

오라클에서 테스트 되었으며, 생각나는대로 작성했습니다.

오라클뿐만 아니라,요즘엔 CTE ( Common Table Expression )이 표준에 포함되어 대부분 지원하니까 다른 벤더제품에서도 응용하면 됩니다.

 

  1. 첫번째
    WITH T1 ( CD , CN1 ,CN2 ,CN3 ,CN4,CN5 ) AS
    (
        SELECT '1001','1','2','3','4','5' FROM DUAL
        UNION ALL
        SELECT '1001','6','7','8','9','10' FROM DUAL
    ),
    T2 AS (
        SELECT LEVEL RN FROM DUAL CONNECT BY LEVEL <=5
    )
    SELECT
        T3.RN,DECODE(T2.RN,1,CN1,2,CN2,3,CN3,4,CN4,CN5) CV
    FROM 
    (
        SELECT
            ROWNUM AS RN,
            CD,CN1,CN2,CN3,CN4,CN5
        FROM T1 
    ) T3,T2
    ORDER BY T3.RN,T2.RN
  2. 두번째
    WITH T1 ( CD , CN1 ,CN2 ,CN3 ,CN4,CN5 ) AS
    (
        SELECT '1001','1','2','3','4','5' FROM DUAL
        UNION ALL
        SELECT '1001','6','7','8','9','10' FROM DUAL
    ),
    T2 AS (
        SELECT LEVEL RN FROM DUAL CONNECT BY LEVEL <=5
    )
    SELECT
       T3.CD,T3.RN,T2.RN,
       SUM(DECODE(T2.RN,1,T3.CN1,2,T3.CN2,3,T3.CN3,4,T3.CN4,T3.CN5)) CV 
    FROM
    (
        SELECT
            ROWNUM AS RN,
            CD,CN1,CN2,CN3,CN4,CN5
        FROM T1 
    ) T3 ,T2
    GROUP BY 
        T3.CD,T3.RN,T2.RN
    ORDER BY 
        T3.CD,T3.RN,T2.RN
  3. 세번째 : 11G부터
    WITH T1 ( CD , CN1 ,CN2 ,CN3 ,CN4,CN5 ) AS
    (
        SELECT '1001','1','2','3','4','5' FROM DUAL
        UNION ALL
        SELECT '1001','6','7','8','9','10' FROM DUAL
    )
    SELECT
        CD,CN,CV
    FROM 
        T1 
    UNPIVOT ( CV FOR CN IN (CN1,CN2,CN3,CN4,CN5) )
반응형
Comments