Pivot 사용하기-1(주영준) SQL
2011.01.15 22:25
1. Pivot 사용하기
주영준책임
1.1. Pivot 구문
(0) [XML]: 가변적인 Value에 대한 Pivoting을 위해 XML Database을 이용 시 정의한다.
(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 생성]
[Pivot구문 사용 JOB_ID,DEPARTMENT_ID별 SUM(SALARY) SQL ]
<그림1-2 Pivot전/후 결과집합비교(Single Column)>
1.2. Pivot Column Aliasing
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절에서 사용해야 한다.
1.4. Pivot Multiple Columns
PIVOT_CLAUSE 에는 1개 이상 의 Grouping함수가 적용된 Column을 정의할 수 있다.
(정의된 Column은 Aliasing이 되어야한다.)
아래처럼, PIVOT_FOR_CLAUSE에도 복수Column Pivot의 기준Column으로 정의할 수 있다. , 이런 경우 PIVOT_IN_CLAUSE 복수 Column에 대한 Filtering 값을 정의해야 한다.
<그림1-3 Pivot전/후 결과집합비교(Multiple Columns)>
※참고문헌: www.oracle-developer.net "pivot and unpivot queries in 11g"
SELECT ... FROM ... PIVOT [XML]--------------(0) (PIVOT_CLAUSE --------------(1) PIVOT_FOR_CLAUSE ------------(2) PIVOT_IN_CLAUSE) -------------(3) WHERE ...

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

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 )



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) )

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

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) )

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) )


- Tag :
- SQL