Oracle 오라클 피벗(2) - PIVOT, UNPIVOT, (행을 열로, 열을 행으로)

[Oracle] 오라클 피벗(2) - PIVOT, UNPIVOT, (행을 열로, 열을 행으로)

안녕하세요. 갓대희 입니다. 이번 포스팅은 [ 오라플 피벗, 두번째 ] 입니다. :)  

 

업무를 하다보면 행을 열으로 전환한다던지, 열을 행으로 전환해야하는 업무가 간혹 발생한다.

이때 보통 decode 또는 case when을 통해 행 => 열 전환을 하였을 것이다.

이를 편하게 해결 할 수 있는 기능이 Oracle 11g부터 제공된다.

 

PIVOT

 - 행을 열로 변환할 때 사용하는 방법으로 기존 GROUP BY 와 집계함수(MAX, SUM 등), DECODE를 사용하는 방법을 대체한다. 코드가 매우 간결 해진다.

 - PIVOT 절은 GROUP Function 을 포함한 계산식을 정의 하며 FOR 절은 값을 구분할 (DECODE 사용시 조건식이 정의될) 컬럼명과 IN 으로 값을 정의한다. 이때 IN 연산자는 Subquery 는 포함 할 수 없다. Alias 정의도 가능하다.

 

UNPIVOT

 - PIVOT 의 반대 개념으로 열을 행으로 변환할 때 사용하는 방법으로 기존 복제용 집합과의 CROSS JOIN 을 통한 행복제 방법과 DECODE 를 조합하는 방법을 대체합니다.

 다음과 같은 예제를 보며 이해 하도록 하자.

( Pivot은 글로만 보면 잘 이해가 안된다. 자주 사용하지 않기 때문에 긁어서 직접 실행해봐야 이해하기 쉽다.!!)

 

 ▶ 1. DECODE와 집계함수를 이용한 피벗

 

with temp_table as
 (select (to_date('20160101', 'YYYYMMDD') + level - 1) dte
    from dual
  connect by level - 1 <= to_date('20171231', 'YYYYMMDD') - to_date('20160101', 'YYYYMMDD'))
select to_char(dte, 'YY') || '년' year,
       sum(decode(to_char(dte, 'MM'), '01', 1, 0)) as "1월",
       sum(decode(to_char(dte, 'MM'), '02', 1, 0)) as "2월",
       sum(decode(to_char(dte, 'MM'), '03', 1, 0)) as "3월",
       sum(decode(to_char(dte, 'MM'), '04', 1, 0)) as "4월",
       sum(decode(to_char(dte, 'MM'), '05', 1, 0)) as "5월",
       sum(decode(to_char(dte, 'MM'), '06', 1, 0)) as "6월",
       sum(decode(to_char(dte, 'MM'), '07', 1, 0)) as "7월",
       sum(decode(to_char(dte, 'MM'), '08', 1, 0)) as "8월",
       sum(decode(to_char(dte, 'MM'), '09', 1, 0)) as "9월",
       sum(decode(to_char(dte, 'MM'), '10', 1, 0)) as "10월",
       sum(decode(to_char(dte, 'MM'), '11', 1, 0)) as "11월",
       sum(decode(to_char(dte, 'MM'), '12', 1, 0)) as "12월"
  from temp_table
 group by to_char(dte, 'YY')
 order by year;

 

 

▶ 2. PIVOT 함수를 이용

 

with temp_table as
 (select (to_date('20160101', 'YYYYMMDD') + level - 1) dte
    from dual
  connect by level - 1 <= to_date('20171231', 'YYYYMMDD') - to_date('20160101', 'YYYYMMDD'))
select *
  from (select to_char(dte, 'YY') || '년' year,
               decode(to_char(dte, 'MM'), '01', '1월', '02', '2월', '03', '3월', '04', '4월', '05', '5월', '06', '6월', '07', '7월', '08', '8월', '09', '9월', '10', '10월', '11', '11월', '12', '12월') as mon,
               dte
          from temp_table)
pivot (count(dte) for mon in('1월', '2월', '3월', '4월', '5월', '6월', '7월', '8월', '9월', '10월', '11월', '12월'))
 order by year;

 

 

▶ 3. UNPIVOT 함수를 위한 쉬운 예제

 

ex) 기본 데이터 (월, 성명)

 

 - 3.1 PIVOT (행 => 열)

with temp_login_his as
 (select '1월' 월,
         '갓댐' 성명
    from dual
  union all
  select '1월' 월,
         '홍길동' 성명
    from dual
  union all
  select '1월' 월,
         '갓댐' 성명
    from dual
  union all
  select '2월' 월,
         '갓댐' 성명
    from dual
  union all
  select '2월' 월,
         '홍길동' 성명
    from dual)
select *
  from temp_login_his
pivot (count(월) for 월 in('1월', '2월'));

 

 

 - 3.2 UNPIVOT (열 => 행)

 

WITH TEMP_LOGIN_HIS AS ( SELECT '갓댐' 성명, '2' 일월, '1' 이월 FROM DUAL UNION ALL SELECT '홍길동' 성명, '1' 일월, '1' 이월 FROM DUAL ) SELECT * FROM TEMP_LOGIN_HIS UNPIVOT ( 횟수 FOR 월 IN (일월, 이월) );with temp_login_his as
 (select '갓댐' 성명,
         '2' 일월,
         '1' 이월
    from dual
  union all
  select '홍길동' 성명,
         '1' 일월,
         '1' 이월
    from dual)
select *
  from temp_login_his unpivot(횟수 for 월 in(일월, 이월));

 

출처: https://goddaehee.tistory.com/58 [갓대희의 작은공간]