Pivot
0. 개요
- 오라클 11g버전에서 제공하기 시작한 기능- 행과 열의 배치를 뒤집는 기능(행 ↔ 열)
1. 사용법
1) 사용틀예)
SELECT *
FROM TABLE
PIVOT (
PIVOT 절
PIVOT FOR 절
PIVOT IN 절
)
| cs |
1-1) PIVOT 절
- 그룹함수가 적용된 column을 정의
- grouping된 결과물을 제공
예) SUM(DSTB_AMT), SUM(DSTB_AMT_BF_MM), SUM(ID_RATE_AMT)
1-2) PIVOT FOR 절
- PIVOT의 기준이 되는 column을 정의
- grouping의 기준을 정해줘야 됨 = GROUP BY절
1-3) PIVOT IN 절
- WHERE절과 같은 필터링의 의미
※ 주의점
- PIVOT은 FROM절에 걸어준 테이블의 모든 column중에
PIVOT절에 기술한 column을 제외하고 GROUP BY를 실행한다
- 따라서, GROUP BY를 할 대상들만 WITH절 또는 Sub Query로
묶은 후 PIVOT을 해야함
2. 예시
1)Sub Query
SELECT
*
FROM
(SELECT
MNGM_ACNT_CD
, MNGM_ACNT_NM
, UPR_ACNT_CD
, MNGM_DEPT_CD
<choose>
<when test = 'amtUnit != null and amtUnit == "1"'>
, SUM(DSTB_AMT) AS DSTB_AMT
, SUM(DSTB_AMT_BF_MM) AS DSTB_AMT_BF_MM
</when>
<when test = 'amtUnit != null and amtUnit == "1000000"'>
, ROUND((SUM(DSTB_AMT)/1000000),2) AS DSTB_AMT
, ROUND((SUM(DSTB_AMT_BF_MM)/1000000),2) AS DSTB_AMT_BF_MM
</when>
<when test = 'amtUnit != null and amtUnit == "100000000"'>
, ROUND((SUM(DSTB_AMT)/100000000),2) AS DSTB_AMT
, ROUND((SUM(DSTB_AMT_BF_MM)/100000000),2) AS DSTB_AMT_BF_MM
</when>
</choose>
, CASE WHEN SUM(DSTB_AMT_BF_MM) != 0 THEN TRUNC(( SUM(DSTB_AMT) / SUM(DSTB_AMT_BF_MM) )*100, 2) ELSE TO_NUMBER('') END AS ID_RATE_AMT
, ACNT_LVL
, PRT_SORT_SEQ
FROM MA_RT_DSTB_SUM_DEPT
WHERE 1=1
<choose>
<when test = 'dvsnCd != null and dvsnCd == "01"'>
AND FACC_YYMM = #{stndYm}
</when>
<when test = 'dvsnCd != null and dvsnCd == "02"'>
AND FACC_YYMM <![CDATA[<]]>= #{stndYm}
AND FACC_YYMM >= TO_CHAR(TO_DATE(#{stndYm},'YYYYMM'),'YYYY')||'01'
</when>
<when test = 'dvsnCd != null and dvsnCd == "03"'>
<choose>
<when test = 'stndYm != null and stndYm.substring(4) != "12"'>
AND FACC_YYMM <![CDATA[<]]>= #{stndYm}
AND FACC_YYMM >= TO_CHAR(ADD_MONTHS(TO_DATE(#{stndYm},'YYYYMM'), -12),'YYYY')||'12'
</when>
<when test = 'stndYm != null and stndYm.substring(4) == "12"'>
AND FACC_YYMM = TO_CHAR(TO_DATE(#{stndYm},'YYYYMM'),'YYYY')||'12'
</when>
</choose>
</when>
</choose>
<if test = ' deptCd != null and deptCd.trim() != ""'> <!-- 복수로 선택한 부서 코드 받아서 조건 설정 -->
AND MNGM_DEPT_CD IN ( ${deptCd})
</if>
GROUP BY MNGM_ACNT_CD
, MNGM_ACNT_NM
, UPR_ACNT_CD
, MNGM_DEPT_CD
, ACNT_LVL
, PRT_SORT_SEQ
)
PIVOT(
SUM(DSTB_AMT) AS DSTB_AMT
, SUM(DSTB_AMT_BF_MM) AS DSTB_AMT_BF_MM
, SUM(ID_RATE_AMT) AS ID_RATE_AMT
FOR MNGM_DEPT_CD
IN (
100000 AS DEPT_100000 /* 본사 */
,100200 AS DEPT_100200 /* 리츠사업부 */
,100220 AS DEPT_100220 /* 투자팀 */
,100230 AS DEPT_100230 /* 운용팀 */
,111000 AS DEPT_111000 /* 경영지원실 */
,111133 AS DEPT_111133 /* 기획팀 */
,111136 AS DEPT_111136 /* 경영지원팀 */
,111139 AS DEPT_111139 /* IT파트 */
,111137 AS DEPT_111137 /* 경영관리팀 */
,111140 AS DEPT_111140 /* 재무팀 */
,111145 AS DEPT_111145 /* RM팀 */
,111147 AS DEPT_111147 /* 사업관리팀 */
,111148 AS DEPT_111148 /* 신탁지원파트 */
,111156 AS DEPT_111156 /* 차세대T/F팀 */
,103000 AS DEPT_103000 /* 준법감시인 */
,101000 AS DEPT_101000 /* 감사 */
,101110 AS DEPT_101110 /* 감사팀 */
,104000 AS DEPT_104000 /* 위험관리책임자 */
,144000 AS DEPT_144000 /* 영업1본부 */
,144200 AS DEPT_144200 /* 1사업부 */
,144210 AS DEPT_144210 /* 영업1팀 */
,144230 AS DEPT_144230 /* 영업3팀 */
,144240 AS DEPT_144240 /* 영업4팀 */
,144220 AS DEPT_144220 /* 영업2팀 */
,144222 AS DEPT_144222 /* 2팀파트 */
,144250 AS DEPT_144250 /* 영업5팀 */
,144260 AS DEPT_144260 /* 영업12팀 */
,144270 AS DEPT_144270 /* 영업13팀 */
,144900 AS DEPT_144900 /* 영업1본부추진역 */
,145000 AS DEPT_145000 /* 영업2본부 */
,145200 AS DEPT_145200 /* 2사업부 */
,145210 AS DEPT_145210 /* 영업6팀 */
,145212 AS DEPT_145212 /* 6팀파트 */
,145220 AS DEPT_145220 /* 영업7팀 */
,145222 AS DEPT_145222 /* 7팀파트 */
,145250 AS DEPT_145250 /* 영업10팀 */
,145230 AS DEPT_145230 /* 영업8팀 */
,145240 AS DEPT_145240 /* 영업9팀 */
,145260 AS DEPT_145260 /* 영업11팀 */
,145270 AS DEPT_145270 /* 영업14팀 */
,145292 AS DEPT_145292 /* 기술지원팀 */
,145900 AS DEPT_145900 /* 영업2본부추진역 */
,149000 AS DEPT_149000 /* 추진역 */
)
)
| cs |
2) WITH절로 묶은 경우
WITH temp01 AS
(
SELECT
MNGM_ACNT_CD /* 계정코드 */
, MNGM_ACNT_NM /* 계정명 */
, ACNT_LVL /* 계정레벨 */
, MNGM_DEPT_CD /* 부서코드 */
, SUM(P_ACCT_AMT) AS P_ACCT_AMT /* 전월실적 */
, SUM(T_ACCT_AMT) AS T_ACCT_AMT /* 당월실적 */
, CASE WHEN SUM(P_ACCT_AMT) != 0 THEN TRUNC (( SUM(T_ACCT_AMT) / SUM(P_ACCT_AMT) )*100, 2) ELSE 0 END AS R_ACCT_AMT /* 증감율 */
FROM
(
SELECT
X.MNGM_ACNT_CD AS MNGM_ACNT_CD /* 계정코드 */
, X.MNGM_ACNT_NM AS MNGM_ACNT_NM /* 계정명 */
, X.MNGM_DEPT_CD AS MNGM_DEPT_CD /* 부서코드 */
, X.ACNT_LVL /* 계정레벨 */ AS ACNT_LVL
, CASE WHEN X.FACC_YYMM = TO_CHAR(ADD_MONTHS(TO_DATE(#{stndYm}, 'YYYYMM'), -1), 'YYYYMM') THEN CASE WHEN Y.DSTB_YN = '1' THEN Y.SAMT ELSE Y.RAMT END ELSE 0 END AS P_ACCT_AMT
, CASE WHEN X.FACC_YYMM = #{stndYm} THEN CASE WHEN Y.DSTB_YN = '1' THEN Y.SAMT ELSE Y.RAMT END ELSE 0 END AS T_ACCT_AMT
FROM (
SELECT /* inline view */
A.FACC_YYMM /* 결산년월 */
, A.MNGM_DEPT_CD /* 부서코드 */
, A.MNGM_DEPT_NM /* 부서명 */
, B.UPR_ACNT_CD /* 상위계정코드 */
, B.MNGM_ACNT_CD /* 계정코드 */
, B.MNGM_ACNT_NM /* 계정명 */
, B.PRT_SORT_SEQ /* 정렬순번 */
, B.ACNT_LVL /* 계정레벨 */
FROM MA_MI_DEPT A /* 관리회계부서테이블 */
, MA_MI_ACNT B /* 관리회계계정테이블 */
WHERE A.FACC_YYMM IN (TO_CHAR(ADD_MONTHS(TO_DATE(#{stndYm}, 'YYYYMM'), -1), 'YYYYMM'), #{stndYm})
AND A.FACC_YYMM = B.FACC_YYMM /* 동일한부서와계정조건으로JOIN */
ORDER BY A.MNGM_DEPT_CD, B.PRT_SORT_SEQ ) X /* 부서 - 계정JOIN*/
,(
SELECT
A.FACC_YYMM
, DECODE(B.RDEPT_CD,NULL,'1','2') /* 배부여부 1: 미배부, 2: 배부 */ AS DSTB_YN
, A.SACCT_CD /* sender계정코드 */
, F_MA_ACNT_NM(A.FACC_YYMM,A.SACCT_CD) /* 계정명 */ AS SACCT_NM
, A.SDEPT_CD /* sender부서코드 */
, F_MA_DEPT_NM(A.FACC_YYMM,A.SDEPT_CD) /* 부서명 */ AS SDEPT_NM
, B.DSTB_HEDR_CD /* 기준정보: 배부헤더코드 */
, B.DSTB_STDR /* 기준정보: 배부기준 */
, B.DSTB_STDR_DETL /* 기준정보: 배부기준상세 */
, DECODE(B.RACCT_CD,NULL,A.SACCT_CD,B.RACCT_CD) AS RACCT_CD /* receiver계정코드: receiver계정코드가 null이면 sender계정코드, 값이 존재하면 receiver계정코드로 세팅 */
, F_MA_ACNT_NM(A.FACC_YYMM,B.RACCT_CD) AS RACCT_NM
, DECODE(B.RDEPT_CD,NULL,SDEPT_CD,B.RDEPT_CD) AS RDEPT_CD /* receiver부서코드: receiver부서코드가 null이면 sender부서코드, 값이 존재하면 receiver부서코드로 세팅 */
, F_MA_DEPT_NM(A.FACC_YYMM,B.RDEPT_CD) AS RDEPT_NM
, NVL(A.S_AMT,0) /* sender합계 */ AS SAMT
, NVL(B.R_AMT,0) /* receiver합계 */ AS RAMT
FROM
(
SELECT /* inline view1-1 */
FACC_YYMM /* 결산년월 */
, ADJ_BELG_ACNT_CD /* sender계정코드: 조정계정코드 */ AS SACCT_CD
, ADJ_BELG_DEPT_CD /* sender부서코드: 조정부서코드 */ AS SDEPT_CD
, SUM(DSTB_AMT) /* sender합계: 배부금액합 */ AS S_AMT
FROM MA_RT_DRT_PCST_DETL /* 관리회계직접원가상세테이블 */
WHERE 1=1
AND FACC_YYMM IN (TO_CHAR(ADD_MONTHS(TO_DATE(#{stndYm}, 'YYYYMM'), -1), 'YYYYMM'), #{stndYm})
AND CMPN_CD = '1000'
GROUP BY FACC_YYMM, ADJ_BELG_ACNT_CD, ADJ_BELG_DEPT_CD ) A /* 직접원가 조정분 금액 */
,(
SELECT
FACC_YYMM
, MNGM_ACNT_CD /* 계정코드 */ AS ACCT
, MNGM_DEPT_CD /* 부서코드 */ AS DEPT
, ADJ_BELG_ACNT_CD /* 조정계정코드 */ AS RACCT_CD
, ADJ_BELG_DEPT_CD /* 조정부서코드 */ AS RDEPT_CD
, DSTB_HEDR_CD /* 배부헤더코드 */
, DSTB_STDR /* 배부기준 */
, DSTB_STDR_DETL /* 배부기준상세 */
, SUM(DSTB_AMT) /* 배부금액합 */ AS R_AMT
FROM MA_RT_IDRT_PCST_DETL /* 관리회계간접원가상세테이블 */
WHERE 1=1
AND FACC_YYMM IN (TO_CHAR(ADD_MONTHS(TO_DATE(#{stndYm}, 'YYYYMM'), -1), 'YYYYMM'), #{stndYm})
AND CMPN_CD = '1000' /* 회사코드 = 1000: 고유회계 */
GROUP BY FACC_YYMM, MNGM_ACNT_CD, MNGM_DEPT_CD, ADJ_BELG_ACNT_CD
, ADJ_BELG_DEPT_CD, DSTB_HEDR_CD, DSTB_STDR, DSTB_STDR_DETL
) B /* 간접원가 배부 받은 금액 */
WHERE 1=1
AND A.SACCT_CD = B.ACCT(+)
AND A.SDEPT_CD = B.DEPT(+)
ORDER BY 3, 5 ) Y /* 직접원가 조정부 + 간접원가 배부 받은금액 */
WHERE 1=1
AND X.FACC_YYMM IN (TO_CHAR(ADD_MONTHS(TO_DATE(#{stndYm}, 'YYYYMM'), -1), 'YYYYMM'), #{stndYm})
AND X.FACC_YYMM = Y.FACC_YYMM(+)
AND X.MNGM_ACNT_CD = Y.RACCT_CD(+)
AND X.MNGM_DEPT_CD = Y.RDEPT_CD(+)
ORDER BY X.MNGM_DEPT_CD, X.PRT_SORT_SEQ
)
GROUP BY MNGM_ACNT_CD, MNGM_ACNT_NM, ACNT_LVL, MNGM_DEPT_CD
)
SELECT *
FROM TEMP01
PIVOT ( SUM(T_ACCT_AMT) AS T_ACCT_AMT, SUM(P_ACCT_AMT) AS P_ACCT_AMT, SUM(R_ACCT_AMT) AS R_ACCT_AMT
FOR MNGM_DEPT_CD IN (
'100000'
, '100200'
, '100220'
, '100230'
, '111000'
, '111133'
, '111136'
, '111139'
, '111140'
, '111145'
, '111147'
, '111148'
, '111156'
, '103000'
, '101000'
, '101110'
, '104000'
, '144000'
, '144200'
, '144210'
, '144230'
, '144240'
, '144220'
, '144222'
, '144250'
, '144260'
, '144270'
, '144900'
, '145000'
, '145200'
, '145210'
, '145212'
, '145220'
, '145222'
, '145250'
, '145230'
, '145240'
, '145260'
, '145270'
, '145292'
, '145900'
, '149000'
)
)
| cs |
※ UNPIVOT도 존재