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

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

오라클뿐만 아니라,요즘엔 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) )