행위

"오라클 피봇 Pivot"의 두 판 사이의 차이

DB CAFE

40번째 줄: 40번째 줄:
 
;
 
;
 
</source>
 
</source>
 
 
 
 
 
 
 
  
 
2) PIVOT 함수를 이용
 
2) PIVOT 함수를 이용
92번째 줄: 85번째 줄:
 
</source>
 
</source>
 
   
 
   
 
 
 
 
3) UNPIVOT 함수를 위한 쉬운 예제
 
3) UNPIVOT 함수를 위한 쉬운 예제
  
 
ex) 기본 데이터 =>
 
ex) 기본 데이터 =>
 
 
 
 
 
 
  
 
3.1 PIVOT (행 => 열)
 
3.1 PIVOT (행 => 열)
 
  
 
<source lang=sql>
 
<source lang=sql>
117번째 줄: 100번째 줄:
 
)
 
)
 
SELECT * FROM TEMP_LOGIN_HIS
 
SELECT * FROM TEMP_LOGIN_HIS
PIVOT ( COUNT(월) FOR 월 IN ('1월', '2월') );
+
PIVOT (  
 +
        COUNT(월) -- 그룹핑 값
 +
          FOR 월 -- 그룹핑 대상
 +
          IN ('1월', '2월') ) -- 필터링 조건
 +
;
 
</source>
 
</source>
 
 
 
 
 
 
 
  
 
3.2 UNPIVOT (열 => 행)
 
3.2 UNPIVOT (열 => 행)
 
 
 
 
ex) 언피벗
 
ex) 언피벗
 
성명, '2' 일월, '1' 이월 FROM DUAL UNION ALL
 
성명, '2' 일월, '1' 이월 FROM DUAL UNION ALL

2022년 11월 8일 (화) 07:56 판

thumb_up 추천메뉴 바로가기


[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 를 조합하는 방법을 대체합니다.

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

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 
      (
        -- group 값
        COUNT (DTE)
        -- group 대상
          FOR MON 
        -- 필터링
           IN ('1월', '2월', '3월', '4월', '5월', '6월', '7월', '8월', '9월', '10월', '11월', '12월')
      )
ORDER BY YEAR
;
  • 피봇은 FROM 절에 걸어준 테이블의 모든 컬럼 중 PIVOT 절에 기술한 컬럼을 제외하고 모두 GROUP BY 해버린다.

즉, GROUP BY 할 대상들만 Sub-Query 또는 With 절로 묶어서 추려낸 뒤 피봇을 해야 한다.

WITH TEMP AS (
   SELECT DEPTNO, SAL
    FROM EMP
)
SELECT *
  FROM TEMP
 PIVOT(
        SUM(SAL)
        FOR DEPTNO
        IN (10, 20, 30)
      );

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 (열 => 행) ex) 언피벗 성명, '2' 일월, '1' 이월 FROM DUAL UNION ALL

   SELECT '홍길동' 성명, '1' 일월, '1' 이월 FROM DUAL

)

SELECT * FROM TEMP_LOGIN_HIS
UNPIVOT (  횟수 FOR 월 IN (일월, 이월) );