-
PIVOT , UNPIVOT데이터베이스 2021. 5. 16. 22:27
PIVOT
PIVOT 은 회전시킨다는 의미를 갖고 있다.
PIVOT 절은 행을 열로 회전시킨다.
PIVOT [ XML ] ( aggregate_function (expr) [[AS] ALIAS] // 집계할 열을 지정 [,aggregate_function (expr) [[AS] ALIAS] ] FOR { column | (column[,column..])} // PIVOT 할 열을 지정 IN ({ { {expr | (expr[,expr]..)} [[AS] alias ]} // PIVOT 할 열 값을 지정 | subquery | ANY [,ANY] }) )
aggregate function : 집계할 열을 지정
FOR : PIVOT 할 열을 지정
IN : PIVOT 할 열 값을 지정
SELECT * FROM ( SELECT JOB, DEPTNO, SAL FROM EMP PIVOT ( SUM(SAL) // 집계할 열을 지정 FOR DEPTNO // PIVOT할 열을 지정 IN (10,20,30) // PIVOT할 열 값을 지정 ) ORDER BY 1; =============================================== JOB 10 20 30 ----- ---- ---- ---- ANALYST 6000 CLERK 1300 1900 950 MANAGER 2450 2975 2850 PRESIDENT 5000 SALESMAN 5000
집계함수과 IN 절에 지정한 별칭에 따라 아래와 같은 규칙으로 열 명이 부여된다.
집계함수와 IN 절 모두 별칭을 지정하는 것이 바람직하다.
10 10 as D10 SUM(SAL) 10 D10 SUM(SAL) AS sal 10_sal D10_sal 조건문 부여 예시
SELECT JOB, D20_SAL FROM ( SELECT JOB, DEPTNO, SAL FROM EMP PIVOT ( SUM(SAL) AS SAL FOR DEPTNO IN (10 AS D10, 20 AS D20, 30 AS D30) WHERE D20_SAL > 2500 ) ORDER BY 1; =============================================== JOB D20_SAL ----- -------- ANALYST 6000 MANAGER 2975
다수의 집계함수 사용 예시
SELECT JOB, D20_SAL FROM ( SELECT JOB, DEPTNO, SAL FROM EMP PIVOT ( SUM(SAL) AS SAL ,COUNT(*) AS CNT FOR DEPTNO IN (10 AS D10, 20 AS D20, 30 AS D30) ) ORDER BY 1; =============================================== JOB D10_SAL D10_CNT D20_SAL D20_CNT ----- ------- ------- ------- ------- ANALYST 0 6000 2 CLERK 1300 1 1900 2 MANAGER 2450 1 2975 1 ...
FOR 절에 다수의 열 기술 예지
SELECT JOB, D20_SAL FROM ( SELECT TO_CHAR(HIREDATE,'YYYY') AS YYYY, JOB, DEPTNO, SAL FROM EMP PIVOT ( SUM(SAL) AS SAL ,COUNT(*) AS CNT FOR ( DEPTNO, JOB ) IN ( (10,'ANALYST') AS D10A ,(10,'CLERK') AS D10C ,(20,'ANALYST') AS D20A ,(20,'CLERK') AS D20C ) ) ORDER BY 1; =================================================== YYYY D10A_SAL D10A_CNT D10C_SAL D10C_CNT D20A_SAL D20A_CNT D20C_SAL D20C_CNT ----- -------- -------- -------- -------- -------- -------- -------- -------- 1980 1981 ............... 1982
PIVOT 대신 집계함수와 CASE 표현식 사용
================================================================ CASE 문 ================================================================ SELECT JOB , SUM (CASE DEPTNO WHEN 10 THEN SAL END) AS D10_SAL , SUM (CASE DEPTNO WHEN 20 THEN SAL END) AS D20_SAL , SUM (CASE DEPTNO WHEN 30 THEN SAL END) AS D30_SAL FROM EMP GROUP BY JOB ORDER BY JOB; ================================================================ PIVOT 절 ================================================================ SELECT * FROM ( SELECT JOB, DEPTNO, SAL FROM EMP PIVOT ( SUM(SAL) FOR DEPTNO IN (10 AS D10, 20 AS D20, 30 AS D30) ) ORDER BY 1; ================================================================ JOB D10_SAL D20_SAL D30_SAL ----- ------- ------- ------- ANALYST 6000 CLERK 1300 1900 950 MANAGER 2450 2975 2850 PRESIDENT 5000 SALESMAN 5000
UNPIVOT
UNPIVOT 절은 열을 행으로 회전시킨다.
UNPIVOT [ { INCLUE | EXCLUDE } NULLS ] ( { column | (column[,col..]..) } // UNPIVOT 된 값이 들어갈 열을 지정 FOR { column | (column[,col..]) } // UNPIVOT 된 값을 설명할 값이 들어갈 열을 지정 IN ( // UNPIVOT할 열과 설명할 값의 리터럴 값을 지정 { column | (column[,col..]) } AS { literal | literal[,literal]..} ,[{ column | (column[,col..]) } AS { literal | literal[,literal]..}] ) )
UNPIVOT column : UNPIVOT 된 값이 들어갈 열을 지정
FOR 절 : UNPIVOT 된 값을 설명할 값이 들어갈 열을 지정
IN 절 : UNPIVOT할 열과 설명할 값의 리터럴 값을 지정
//테이블 T1 내부 SELECT * FROM T1 ORDER BY JOB; =========================================== JOB D10_SAL D20_SAL D30_SAL ----- ------- ------- ------- ANALYST 6000 CLERK 1300 1900 950 MANAGER 2450 2975 2850 PRESIDENT 5000 SALESMAN 5000
SELECT JOB, DEPTNO, SAL FROM T1 UNPIVOT ( SAL // UNPIVOT 된 값이 들어갈 열을 지정 FOR DEPTNO // UNPIVOT 된 값을 설명할 값이 들어갈 열을 지정 IN (D10_SAL, D20_SAL) // UNPIVOT할 열과 설명할 값의 리터럴 값을 지정 ) ORDER BY 1,2; =================================================== JOB DEPTNO SAL ====== ====== ==== ANALYST D20_SAL 6000 CLERK D10_SAL 1300 CLERK D20_SAL 1900
UNPIVOT 도 PIVOT 과 마찬가지로 다수의 집계 함수를 지원하고 다수의 열을 가질 수 있다.
UNPIVOT 을 사용할 수 없는 경우 카디션 곱을 통해 수행할 수 있다.
UNPIVOT 랑 열의 개수만큼 행을 복제하고, CASE 표현식으로 UNPIVOT 랑 열을 선택하는 방식이다.
SELECT A.JOB , CASE B.LV WHEN 1 THEN 10 WHEN 2 THEN 20 END AS DEPTNO , CASE B.LV WHEN 1 THEN A.D10_SAL WHEN 2 THEN A.D20_SAL END AS SAL , CASE B.LV WHEN 1 THEN A.D10_CNT WHEN 2 THEN A.D20_CNT END AS CNT FROM T1 A ,( SELECT LEVEL AS LV FROM DUAL CONNECT BY LEVEL <=2 ) B ORDER BY 1, 2; =================================================== JOB DEPTNO SAL CNT ====== ====== ==== ==== ANALYST 10 0 ANALYST 20 6000 0 CLERK 10 1300 2 CLERK 20 1900 1
출처
SQL 전문가 가이드 - 한국데이터산업진흥원
반응형