추천 스크랩 URL복사

1. Pivot 사용하기

주영준책임

1.1. Pivot 구문

SELECT ... 
FROM ... 
PIVOT [XML]--------------(0)
            (PIVOT_CLAUSE --------------(1)
              PIVOT_FOR_CLAUSE ------------(2)
              PIVOT_IN_CLAUSE) -------------(3)
WHERE ...

(1) PIVOT_CLAUSE : Group함수(Sum,Max,Min,Count…)가 적용된 Column을 정의한다.

(2) PIVOT_FOR_CLAUSE: Pivot의 기준이 되는 Column을 정의한다.

(3) PIVOT_IN_CLAUSE: PIVOT_FOR_CLAUSE에서 정의한 Column의 Filtering 값을 정의한다.

   (PIVOT_FOR_CLAUSE에서 정의한 Column의 Distinct Value 안에서 정의 할 수 있다.) 

                        <그림1-1 Pivot결과집합>

[샘플Table 생성]

CREATE TABLE PIVOT_T 
AS
SELECT   EMPLOYEE_ID
       , FIRST_NAME
       , LAST_NAME
       , JOB_ID
       , DEPARTMENT_ID
       , SALARY
FROM   EMPLOYEES
WHERE  JOB_ID IN ('ST_MAN','PU_MAN','SH_CLERK'
                        ,'FI_ACCOUNT','FI_MGR','PU_CLERK'
                        ,'SA_MAN','SA_REP','ST_CLERK')    
AND    DEPARTMENT_ID IN (30,50,80,100) 

[일반적인 JOB_ID, DEPARTMENT_ID 별 SUM(SALARY) SQL]

SELECT   JOB_ID
          , DEPARTMENT_ID
          , SUM(SALARY)
FROM    PIVOT_T
GROUP BY JOB_ID, DEPARTMENT_ID

[Pivot구문 사용 JOB_ID,DEPARTMENT_ID별 SUM(SALARY) SQL ]

WITH PIVOT_DATA AS (
     SELECT   JOB_ID
            , DEPARTMENT_ID
            , SALARY
     FROM     PIVOT_T
)
SELECT   *
FROM     PIVOT_DATA
PIVOT  (
         SUM(SALARY)              -- PIVOT_CLAUSE
         FOR (DEPARTMENT_ID)     -- PIVOT_FOR_CLAUSE
         IN (50,80,100)              -- PIVOT_IN_CLAUSE
       )

                         <그림1-2 Pivot전/후 결과집합비교(Single Column)>

 1.2. Pivot Column Aliasing

PIVOT  (
         SUM(SALARY) 
         FOR (DEPARTMENT_ID) IN (50,80,100)           
       )

PIVOT  (
         SUM(SALARY) AS SAL_SUM
         FOR (DEPARTMENT_ID) IN (50 AS DEPT_50,80 AS DEPT_80,100 AS DEPT_100)           
       )

PIVOT  (
         SUM(SALARY) 
         FOR (DEPARTMENT_ID) IN (50 AS DEPT_50,80 AS DEPT_80,100 AS DEPT_100)           
       )

PIVOT  (
         SUM(SALARY) AS SAL_SUM
         FOR (DEPARTMENT_ID) IN (50,80,100)           
       )

 1.3. Pivot “Group By” Operation

 Pivot 은 내부적으로 PIVOT_CLAUSE에 정의하지 않은 나머지 Column들에 대해서 모두

 “Group By”를 수행한다. 그러므로, Sub Query Factoring, In-Line View, Stored View등을

이용하여 필수 Data Set 으로 가공 후 해당 Data Set을 From절에서 사용해야 한다. 

SELECT   *
FROM     PIVOT_T
PIVOT  (
         SUM(SALARY)
         FOR (DEPARTMENT_ID) IN (50,80,100)           
       )

1.4. Pivot Multiple Columns

PIVOT_CLAUSE 에는 1개 이상 의 Grouping함수가 적용된 Column을 정의할 수 있다.

(정의된 Column은  Aliasing이 되어야한다.)

WITH PIVOT_DATA AS (
     SELECT   JOB_ID
            , DEPARTMENT_ID
            , SALARY
     FROM     PIVOT_T
)
SELECT   *
FROM     PIVOT_DATA
PIVOT  (
         SUM(SALARY) AS SAL_SUM, MAX(SALARY) AS SAL_MAX
         FOR (DEPARTMENT_ID) IN (50,80,100)           
       )

 아래처럼, PIVOT_FOR_CLAUSE에도 복수Column Pivot의 기준Column으로 정의할 수 있다. , 이런 경우 PIVOT_IN_CLAUSE 복수 Column에 대한 Filtering 값을 정의해야 한다.

WITH PIVOT_DATA AS (
     SELECT   JOB_ID
            , DEPARTMENT_ID
            , SALARY
     FROM     PIVOT_T
)
SELECT   *
FROM     PIVOT_DATA A
PIVOT  (
         SUM(SALARY) AS SAL_SUM, MAX(SALARY) AS SAL_MAX
         FOR (DEPARTMENT_ID,JOB_ID) IN ((50,'ST_MAN') AS DEPT_JOB1
,(80,'SA_MAN') AS DEPT_JOB2 
,(100,'FI_ACCOUNT') AS DEPT_JOB3)           
       )

                                       <그림1-3 Pivot전/후 결과집합비교(Multiple Columns)>

※참고문헌: www.oracle-developer.net "pivot and unpivot queries in 11g" 

추천 스크랩 URL복사