[Oracle] WITH AS~ 구문과 PIVOT 함수 활용

WITH AS : 하나의 가상 테이블을 생성하는 구문

 

WITH [가상테이블명] AS

(

    SELECT JOB, DEPT_NO, NAME, SAL

    FROM EMP   //가상 테이블에 들어갈 데이터

)

 

PIVOT 함수 : 세로로 조회된 데이터를 가로로 변환시켜주는 함수

  

EX) PIVOT(MAX 함수 활용)

 

JOB 테이블

NAME	JOB_CODE	JOB	WORK_STRT_DATE	WORK_END_DATE
밍크스1	01	삼성	20100101	20101231
밍크스1	02	LG	20110101	20111231
밍크스1	03	도레이	20120101	20121231
밍크스1	04	코오롱	20130101	20131231

- 이름이 밍크스 1이란 사람의 근무 지역들과 근무일자 데이터가 있습니다.

 

- 4개의 행이 코드별로 나오고 이 행들을 PIVOT 함수를 통해서 하나의 행으로 변환 해보겠습니다.

 

WITH TMP AS (     SELECT NAME //GROUP BY 대상이 될 이름컬럼             , JOB_CODE // PIVOT 안에 FOR IN 구문에 들어갈 조건             , WORK_STRT_DATE             , WORK_END_DATE     FROM JOB )   SELECT * FROM TMP     PIVOT (     MAX(WORK_STRT_DATE) AS "근무시작일자" , MAX(WORK_END_DATE) AS "근무종료일자"     FOR DEPTNO IN ("01" AS "삼성", "02" AS "LG", "03" AS "도레이", "04" AS "코오롱")     )

 

              

결과값

NAME	삼성_근무시작일자	삼성_근무종료일자	LG_근무시작일자	LG_근무종료일자	도레이_근무시작일자	도레이_근무종료일자	코오롱_근무시작일자	코오롱_근무종료일자
밍크스1	20100101	20101231	20110101	20111231	20120101	20121231	20130101	20131231

- 이 경우에는 GROUP BY를 사용 하여 할시에는 하나하나 조회를 해야되는 불편함이 있지만

  PIVOT을 사용 하면 여러 조건들에 해당하는 데이터들을 하나로 묶어 조회할 수 있습니다.

 

EX) PIVOT(SUM 함수 활용)

 

EMP 테이블

 

JOB	DEPTNO	SUM_SAL
SAMSUNG	10	5000
SAMSUNG	10	5000
SAMSUNG	20	6000
SAMSUNG	20	6000
SAMSUNG	30	7500
SAMSUNG	30	7500
LG	10	7500
LG	10	7500
LG	20	7000
LG	20	7000
LG	30	6000
LG	30	6000

SELECT JOB, DEPTNO, SUM(SAL) AS "SUM_SAL" FROM EMP GROUP BY JOB, DEPTNO

 

결과값

JOB	DEPTNO	SUM_SAL
SAMSUNG	10	10000
LG	10	15000
SAMSUNG	20	12000
SAMSUNG	30	15000
LG	20	14000
LG	30	12000

 

- GROUP BY 결과 JOB별로 DEPTNO가 10인 부서의 합계 20인부서의 합계 30인 부서의 합계가 조회가됬습니다.

 

- 여기서 PIVOT을 이용해 JOB별로 3개의 행이아닌 1개의 행으로 만들어보겠습니다.

 

WITH TMP2 AS (     SELECT JOB             , DEPTNO             , SUM_SAL     FROM EMP )   SELECT * FROM TMP2     PIVOT (         SUM(SUM_SAL) AS "합계"         FOR DEPTNO IN (10,20,30)     )

 

결과값

JOB	합계_10	합계_20	합계_30
SAMSUNG	10000	12000	15000
LG	15000	14000	12000

 

- 이쿼리의 결과값을 통해 개발 시에 간편하고 쉽게 데이터를 응용 하실 수 있습니다.