오라클 함수인 Pivot 및 Unpivot 기능은 Oracle 11g 버전부터 제공하는 강력한 함수이다. 피봇함수가 지원되기 이전에는 decode, case문을 이용해서 행과 열을 뒤집어야 했지만, 피봇함수가 지원되면서 행과 열을 간결하게 뒤집을 수 있다. 피봇 함수를 사용한다해도 피봇할 컬럼들은 미리 정의를 해놓아야 하지만, 조금 더 간결하게 코딩할 수 있어서 좋다. 지금부터 오라클 11g Pivot 함수에 대해서 GROUP BY 절과 연관지어서 자세히 설명하도록 한다.

 

 


 

오라클(Oracle) 언피봇(Unpivot) 사용 방법: http://wookoa.tistory.com/241

 


 

 

설명에 사용할 예시 데이터는 오라클에서 교육용으로 제공해주는 EMP 테이블이며, 자세한 데이터는 아래와 같다. EMP 테이블은 인터넷에 검색만해도 스크립트를 쉽게 얻을 수 있다.

 

 

피봇의 구문은 아래의 사진과 같이 PIVOT, PIVOT FOR, PIVOT IN 총 3개의 절이 필요하다.

각 절의 의미에 대해서 알아가면서 하나씩 적용해 볼 것이다.

 

 

 

-. PIVOT 절: 그룹함수가 적용된 컬럼을 정의

피봇함수는 내부적으로 그룹핑된 결과를 제공한다. 그렇기 때문에 PIVOT 절에서 그룹핑된 결과를 정의해야 하는데, 마치 GROUP BY가 들어간 쿼리에서 SELECT SUM(SAL), AVG(SAL) 구절과 같다. PIVOT 절에서는 그룹핑할 대상에 대해서 정의를 해주면 된다. PIVOT 절의 예시로 아래의 사진에서 숫자 값을 예로 들 수 있다. 각 숫자 값들은 부서번호 10, 20, 30에 대한 각각의 그룹핑 결과다. 물론, JOB 컬럼 때문에 조금 더 세분화 되었지만 지금은 부서번호 별로 그룹핑 되었다는 사실까지만 인지하도록 한다. 아래의 결과물을 얻기 위해 본인은 PIVOT 절에 'SUM(SAL)'을 입력했다.

 

 

 

-. PIVOT FOR 절: 피봇의 기준의 되는 컬럼을 정의

PIVOT 절이 그룹핑된 결과물을 제공한다는 사실은 이해했다. 그렇다면 어떠한 기준들로 그룹핑 할 것인지를 PIVOT FOR 절에서 정의해줘야 한다. 마치, GROUP BY 절에 뒤따르는 컬럼과 같은 역할로 이해하면 된다. 아래의 결과물은 피봇과 관계없이 DEPTNO 컬럼을 GROUP BY한 결과다.

 

'SELECT DEPTNO, SUM(SAL) FROM EMP GROUP BY DEPTNO'

 

 

위의 SQL 중 SELECT 절의 SUM(SAL)이 PIVOT 절의 역할이라면, GROUP BY DEPTNO은 PIVOT FOR의 역할이다. 아래의 결과물을 얻기 위해 본인은 PIVOT FOR 절에 'DEPTNO'을 입력했다.

 

 

 

-. PIVOT IN 절: PIVOT FOR절에서 정의한 컬럼에서 필터링을 정의

지금까지 PIVOT FOR 절에서 그룹핑하고, 그룹핑한 결과물을 PIVOT 절로 출력하는 개념까지 도달했다. PIVOT IN 절은 WHERE 절과 같은 필터링 역할이다. 아래의 사진과 같이 피봇과 관련없는 결과물을 또 준비했다. 아래의 결과물은 WHERE 필터링을 추가해서 부서번호 10, 20만 출력하도록 했다.

 

'SELECT DEPTNO, SUM(SAL) FROM EMP WHERE DEPTNO IN (10, 20) GROUP BY DEPTNO'

 

위의 SQL 중 WHERE IN(10, 20)은 PIVOT IN 절의 역할이다. 아래의 결과물을 얻기 위해 본인은 PIVOT IN 절에 '(10, 20, 30)'을 입력했다. 물론, 현재 테이블의 존재하는 모든 부서번호를 입력했다.

 

 

 

지금까지 내용을 정리해보자면 결과적으로 각각의 절에 아래와 같이 입력했다.

" DEPTNO 컬럼으로 그룹핑하되, 그룹핑의 결과 값은 SUM(SAL)로 표현하며, DEPTNO IN (10, 20, 30)의 필터를 걸어주세요 "

 

 

 

이제 피봇 함수를 읽어 내리는데 큰 어려움이 없을 것 이다. 다만, 피봇을 구현하기 위해 한가지만 더 기억해야 한다. 바로, 데이터의 정제다. 위의 피봇 함수를 EMP 테이블에 그대로 걸어서 사용하면 전혀 다른 결과가 나온다. 이유는 PIVOT 절에 있다. 피봇은 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)
      );

 

 

 

 

위와 같은 PIVOT 절의 성질을 이해하면 여러가지 리포팅 화면을 작성할 수 있다. With 절에 JOB 컬럼을 추가하면 아래와 같이 JOB 별로 세분화된 GROUP BY 결과를 볼 수 있다. 물론, 세분화를 하고 싶은 만큼 Sub-Query 또는 With 절에 컬럼을 계속 추가할 수 있다. 

 

 

 

여기까지가 피봇에 대한 이야기의 끝이다.

추가로 연습해 보자면, PIVOT 절 PIVOT IN 절에 Alias를 줄 수 있다. 아래의 사진은 PIVOT IN 절에 별칭을 넣은 예제다.

 

 

 

궁금증을 더해서, 우리가 GROUP BY할 때 SELECT 절에 그룹함수를 한개만 쓰지는 않는다. 두개 이상을 기입해도된다. PIVOT 함수로 동일할지 궁금하다.

 

 

 

비교적 쉬운 방법으로 접근하려고 노력했다. GROUP BY 절과 PIVOT은 개념이 비슷한점이 많은 듯 싶다. 본인이 전달하려고한 내용이 잘 전달되었는지 걱정이다. 오랜시간 고민하고 작성한 PIVOT 함수에 대한 가이드 포스팅을 이로써 마무리 짓도록 한다.

 

 

  1. 좋은 정보 잘 보고 가용!! 천천히읽어보니 개념정리에 큰도움이 되었어용^.^

  2. 와우... 2018.11.23 15:51
    진짜 정성이 가득한 글입니다. 사랑합니다. 세 번정도 더 읽어보면 이해 할 것 같습니다. 감사합니다. 사랑합니다.
    • 다소 주관적이 관점으로 독특한 시각으로 설명해 봤습니다. 도움이 되셨다니 정말 다행입니다! :-)

  3. 대충 일년만에 오네용!!
    즐겨찾기 해놓았다가 필요할때마다 읽어봅니당! 감사합니다!~~

  4. 도움많이 되었습니다~

  5. 풀스택 2019.08.21 16:42
    정말 엄청난 글이네요 PIVOT에 대한 이해에 큰 도움이 되었습니다. 감사합니다.

  6. 감사합니다 저희 교수님이란은 비교도안되게 자루가르쳐주시네요

Private comment