행과 열 바꾸기

 

 

문제)아래 데이터를 세로로 출력해 봅시다.

with my_table as(
SELECT
   MAX(GUBUN) GUBUN, MAX(M) M,  MAX(M2) M2, MAX(DIV) DIV, MAX(PER) PER
   FROM    
     (
   SELECT '멋쟁이' GUBUN, '' M, '' M2, '' DIV, '' PER  FROM DUAL
   UNION ALL
   SELECT '' GUBUN, '3630' M, '' M2, '' DIV, '' PER  FROM DUAL
   UNION ALL
   SELECT '' GUBUN, '' M, '2847' M2, '' DIV, '' PER  FROM DUAL
   UNION ALL
   SELECT '' GUBUN, '' M, '' M2, '783' DIV, '' PER  FROM DUAL
   UNION ALL
   SELECT '' GUBUN, '' M, '' M2, '' DIV, '27.5%' PER  FROM DUAL
   )
      UNION ALL
     SELECT
   MAX(GUBUN) GUBUN, MAX(M) M,  MAX(M2) M2, MAX(DIV) DIV, MAX(PER) PER
   FROM
       (
   SELECT '못난이' GUBUN, '' M, '' M2, '' DIV, '' PER  FROM DUAL
   UNION ALL
   SELECT '' GUBUN, '1' M, '' M2, '' DIV, '' PER  FROM DUAL
   UNION ALL
   SELECT '' GUBUN, '' M, '2' M2, '' DIV, '' PER  FROM DUAL
   UNION ALL
   SELECT '' GUBUN, '' M, '' M2, '1' DIV, '' PER  FROM DUAL
   UNION ALL
   SELECT '' GUBUN, '' M, '' M2, '' DIV, '100%' PER  FROM DUAL
   ))
  select *from my_table;

열을 행으로 바꿔야 하기 때문에 connect by level 절을 사용하여 ROW수를 늘려준다.
SQL>select *
        from my_table
       ,(select level lvl from dual connect by level <= 4) n;

 

level별로 group by를 하지않으면 아래와 같이 나온다.

SQL>select decode(lvl,1,'M',2,'M2',3,'DIV',4,'PER') "GUBUN",
                 decode(gubun,'멋쟁이',decode(lvl,1,m,2,m2,3,div,4,per)) "멋쟁이",
                 decode(gubun,'못난이',decode(lvl,1,m,2,m2,3,div,4,per)) "못난이" 
       from my_table,(select level lvl from dual connect by level <= 4) n
       order by lvl;

 

SOL1)DECODE 사용

SQL>select decode(lvl,1,'M',2,'M2',3,'DIV',4,'PER') "GUBUN",
                max(decode(gubun,'멋쟁이',decode(lvl,1,m,2,m2,3,div,4,per))) "멋쟁이",
                max(decode(gubun,'못난이',decode(lvl,1,m,2,m2,3,div,4,per))) "못난이"
      from my_table,(select level lvl from dual connect by level <= 4) n
      group by lvl
      order by lvl;

SOL2)CASE 사용
 SQL>select decode(cnt,1,'M',2,'M2',3,'DIV',4,'PER') GUBUN,
        MAX(CASE WHEN gubun='멋쟁이' AND CNT=1 THEN M
              WHEN gubun='멋쟁이' AND CNT=2 THEN M2
              WHEN gubun='멋쟁이' AND CNT=3 THEN DIV
              WHEN gubun='멋쟁이' AND CNT=4 THEN PER
              END)  멋쟁이,
        MAX(CASE WHEN gubun='못난이' AND CNT=1 THEN M
              WHEN gubun='못난이' AND CNT=2 THEN M2
              WHEN gubun='못난이' AND CNT=3 THEN DIV
              WHEN gubun='못난이' AND CNT=4 THEN PER
              END)  못난이         
  from my_table,(select level cnt from dual connect by level<5)
  GROUP BY CNT
  order by cnt;