날짜를 파라미터로 받고 그 날짜기준으로 8일치의 데이터를 행이아닌 열로 보는 상황
내 케이스는
코스트센터의 유형에 따라 생산성(물량/근무시간)을 아래와 같은 형식으로 뽑아야함
유형의 순서는 정해져있따,,
| 유형 | 센터명 | D+7 | D+6 | D+5 | D+4 | D+3 | D+2 | D+1 | DDay |
| 유형 계 | |||||||||
| . . . |
|||||||||
1. 입력받은 날짜를 CASE WHEN 조건에 넣고, 그걸 ALIAS를 주면 PIVOT 기준 열로 사용 가능하다.
2. 센터의 유형 순서가 정해져있어서 순서를 표현하기 위한 CATE라는 가상 항목을 만들었다.
(10개 이상이라 알파벳으로 순서표현)
3. 유형 계 항목을 위해(UNLOAD_PRODUCTY라는 컬럼 사용불가, SUM(물량), SUM(근무시간)을 따로 계산 必)
UNION으로 가상 행을 만들고 센터코드로 GROUP BY 하는 쿼리로 감싸주었다.
SELECT A.CENT_DIV_FULL AS CENT_DIV
, A.COSTC_CD
, A.COSTC_NM
, DAY0
, DAY1
, DAY2
, DAY3
, DAY4
, DAY5
, DAY6
, DAY7
, RNUM
FROM (
SELECT A.*
, RANK() OVER (PARTITION BY CENT_DIV_FULL ORDER BY COSTC_CD) AS RNUM
FROM (
SELECT CENT_DIV_FULL
, CASE WHEN CENT_DIV_FULL = 'CPG_RDC(상온)' THEN 'A'
WHEN CENT_DIV_FULL = 'CPG_RDC(저온)' THEN 'B'
WHEN CENT_DIV_FULL = '제약_RDC(제약)' THEN 'C'
WHEN CENT_DIV_FULL = '유통_커피프랜차이즈' THEN 'D'
WHEN CENT_DIV_FULL = 'D2C' THEN 'E'
WHEN CENT_DIV_FULL = 'CPG_단독' THEN 'F'
WHEN CENT_DIV_FULL = 'CPG_제조' THEN 'G'
WHEN CENT_DIV_FULL = '유통_CVS' THEN 'H'
WHEN CENT_DIV_FULL = '유통_단독' THEN 'I'
WHEN CENT_DIV_FULL = '제약_단독' THEN 'J'
WHEN CENT_DIV_FULL = 'F'||CHR(38)||'B' THEN 'K'
WHEN CENT_DIV_FULL = 'AP' THEN 'L'
ELSE 'M'
END AS CATE
, COSTC_CD
, COSTC_NM
, NVL(DAY0, 0) AS DAY0
, NVL(DAY1, 0) AS DAY1
, NVL(DAY2, 0) AS DAY2
, NVL(DAY3, 0) AS DAY3
, NVL(DAY4, 0) AS DAY4
, NVL(DAY5, 0) AS DAY5
, NVL(DAY6, 0) AS DAY6
, NVL(DAY7, 0) AS DAY7
FROM (
SELECT CASE WHEN DD.PRD_Y||DD.PRD_M||DD.PRD_D = TO_CHAR(TO_DATE(#{SEARCH_DTM}, 'YYYY-MM-DD')-7, 'YYYYMMDD') THEN 'DAY0'
WHEN DD.PRD_Y||DD.PRD_M||DD.PRD_D = TO_CHAR(TO_DATE(#{SEARCH_DTM}, 'YYYY-MM-DD')-6, 'YYYYMMDD') THEN 'DAY1'
WHEN DD.PRD_Y||DD.PRD_M||DD.PRD_D = TO_CHAR(TO_DATE(#{SEARCH_DTM}, 'YYYY-MM-DD')-5, 'YYYYMMDD') THEN 'DAY2'
WHEN DD.PRD_Y||DD.PRD_M||DD.PRD_D = TO_CHAR(TO_DATE(#{SEARCH_DTM}, 'YYYY-MM-DD')-4, 'YYYYMMDD') THEN 'DAY3'
WHEN DD.PRD_Y||DD.PRD_M||DD.PRD_D = TO_CHAR(TO_DATE(#{SEARCH_DTM}, 'YYYY-MM-DD')-3, 'YYYYMMDD') THEN 'DAY4'
WHEN DD.PRD_Y||DD.PRD_M||DD.PRD_D = TO_CHAR(TO_DATE(#{SEARCH_DTM}, 'YYYY-MM-DD')-2, 'YYYYMMDD') THEN 'DAY5'
WHEN DD.PRD_Y||DD.PRD_M||DD.PRD_D = TO_CHAR(TO_DATE(#{SEARCH_DTM}, 'YYYY-MM-DD')-1, 'YYYYMMDD') THEN 'DAY6'
WHEN DD.PRD_Y||DD.PRD_M||DD.PRD_D = TO_CHAR(TO_DATE(#{SEARCH_DTM}, 'YYYY-MM-DD'), 'YYYYMMDD') THEN 'DAY7'
ELSE '--'
END AS CRE_DATE
, NVL(DD.UNLD_PRODUCTY, 0) AS UNLD_PRODUCTY
, CASE WHEN M.CENT_DIV = '2' AND M.GRP = '1' THEN 'CPG_RDC(상온)'
WHEN M.CENT_DIV = '4' AND M.GRP = '1' THEN 'CPG_RDC(저온)'
WHEN M.CENT_DIV = '3' AND M.GRP = '5' THEN '제약_RDC(제약)'
WHEN M.CENT_DIV = '7' AND M.GRP = '3' THEN '유통_커피프랜차이즈'
WHEN M.CENT_DIV IN ('9', '10') THEN 'D2C'
WHEN M.CENT_DIV = '1' THEN 'CPG_단독'
WHEN M.CENT_DIV = '5' AND M.GRP = '1' THEN 'CPG_제조'
WHEN M.CENT_DIV = '6' AND M.GRP = '3' THEN '유통_CVS'
WHEN M.CENT_DIV = '8' THEN '유통_단독'
WHEN M.CENT_DIV = '11' THEN '제약_단독'
WHEN M.CENT_DIV = '12' THEN 'F'||CHR(38)||'B'
WHEN M.CENT_DIV = '13' THEN 'AP'
ELSE 'ETC'
END AS CENT_DIV_FULL
, C.CD_NM AS CENT_NM
, M.COSTC_CD
, M.COSTC_NM
FROM TB_DS_PRODUCTY_ADM_DAILY DD
, TB_DS_PRODUCTY_ADM_WORKTIME W
, TB_DS_PRODUCTY M
, TB_CM_CD_DTL C
WHERE DD.IDNO = W.IDNO(+)
AND DD.SEQ_NO = W.SEQ_NO(+)
AND DD.IDNO = M.IDNO
AND C.COM_GRP_CD = 'DASH_CENT_DIV'
AND M.CENT_DIV = C.COM_CD(+)
) A
PIVOT (
SUM(A.UNLD_PRODUCTY) FOR CRE_DATE IN ('DAY0' AS DAY0, 'DAY1' AS DAY1, 'DAY2' AS DAY2, 'DAY3' AS DAY3, 'DAY4' AS DAY4, 'DAY5' AS DAY5, 'DAY6' AS DAY6, 'DAY7' AS DAY7)
)
) A
UNION
SELECT A.*
, 0 AS RNUM
FROM (
SELECT CENT_DIV_FULL
, CASE WHEN CENT_DIV_FULL = 'CPG_RDC(상온)' THEN 'A'
WHEN CENT_DIV_FULL = 'CPG_RDC(저온)' THEN 'B'
WHEN CENT_DIV_FULL = '제약_RDC(제약)' THEN 'C'
WHEN CENT_DIV_FULL = '유통_커피프랜차이즈' THEN 'D'
WHEN CENT_DIV_FULL = 'D2C' THEN 'E'
WHEN CENT_DIV_FULL = 'CPG_단독' THEN 'F'
WHEN CENT_DIV_FULL = 'CPG_제조' THEN 'G'
WHEN CENT_DIV_FULL = '유통_CVS' THEN 'H'
WHEN CENT_DIV_FULL = '유통_단독' THEN 'I'
WHEN CENT_DIV_FULL = '제약_단독' THEN 'J'
WHEN CENT_DIV_FULL = 'F'||CHR(38)||'B' THEN 'K'
WHEN CENT_DIV_FULL = 'AP' THEN 'L'
ELSE 'M'
END AS CATE
, COSTC_CD
, COSTC_NM
, NVL(DAY0, 0) AS DAY0
, NVL(DAY1, 0) AS DAY1
, NVL(DAY2, 0) AS DAY2
, NVL(DAY3, 0) AS DAY3
, NVL(DAY4, 0) AS DAY4
, NVL(DAY5, 0) AS DAY5
, NVL(DAY6, 0) AS DAY6
, NVL(DAY7, 0) AS DAY7
FROM (
SELECT CRE_DATE
, DECODE (SUM(WORKMAN), 0, 0, SUM(GOODS)/SUM(WORKMAN)) AS UNLD_PRODUCTY
, CENT_DIV_FULL
, '구분별 지표' AS COSTC_CD
, '구분별 지표' AS COSTC_NM
FROM (
SELECT CASE WHEN DD.PRD_Y||DD.PRD_M||DD.PRD_D = TO_CHAR(TO_DATE(#{SEARCH_DTM}, 'YYYY-MM-DD')-7, 'YYYYMMDD') THEN 'DAY0'
WHEN DD.PRD_Y||DD.PRD_M||DD.PRD_D = TO_CHAR(TO_DATE(#{SEARCH_DTM}, 'YYYY-MM-DD')-6, 'YYYYMMDD') THEN 'DAY1'
WHEN DD.PRD_Y||DD.PRD_M||DD.PRD_D = TO_CHAR(TO_DATE(#{SEARCH_DTM}, 'YYYY-MM-DD')-5, 'YYYYMMDD') THEN 'DAY2'
WHEN DD.PRD_Y||DD.PRD_M||DD.PRD_D = TO_CHAR(TO_DATE(#{SEARCH_DTM}, 'YYYY-MM-DD')-4, 'YYYYMMDD') THEN 'DAY3'
WHEN DD.PRD_Y||DD.PRD_M||DD.PRD_D = TO_CHAR(TO_DATE(#{SEARCH_DTM}, 'YYYY-MM-DD')-3, 'YYYYMMDD') THEN 'DAY4'
WHEN DD.PRD_Y||DD.PRD_M||DD.PRD_D = TO_CHAR(TO_DATE(#{SEARCH_DTM}, 'YYYY-MM-DD')-2, 'YYYYMMDD') THEN 'DAY5'
WHEN DD.PRD_Y||DD.PRD_M||DD.PRD_D = TO_CHAR(TO_DATE(#{SEARCH_DTM}, 'YYYY-MM-DD')-1, 'YYYYMMDD') THEN 'DAY6'
WHEN DD.PRD_Y||DD.PRD_M||DD.PRD_D = TO_CHAR(TO_DATE(#{SEARCH_DTM}, 'YYYY-MM-DD'), 'YYYYMMDD') THEN 'DAY7'
ELSE '--'
END AS CRE_DATE
,SUM(DD.GOODS) AS GOODS
,SUM(W.WORKMAN) AS WORKMAN
, CASE WHEN M.CENT_DIV = '2' AND M.GRP = '1' THEN 'CPG_RDC(상온)'
WHEN M.CENT_DIV = '4' AND M.GRP = '1' THEN 'CPG_RDC(저온)'
WHEN M.CENT_DIV = '3' AND M.GRP = '5' THEN '제약_RDC(제약)'
WHEN M.CENT_DIV = '7' AND M.GRP = '3' THEN '유통_커피프랜차이즈'
WHEN M.CENT_DIV IN ('9', '10') THEN 'D2C'
WHEN M.CENT_DIV = '1' THEN 'CPG_단독'
WHEN M.CENT_DIV = '5' AND M.GRP = '1' THEN 'CPG_제조'
WHEN M.CENT_DIV = '6' AND M.GRP = '3' THEN '유통_CVS'
WHEN M.CENT_DIV = '8' THEN '유통_단독'
WHEN M.CENT_DIV = '11' THEN '제약_단독'
WHEN M.CENT_DIV = '12' THEN 'F'||CHR(38)||'B'
WHEN M.CENT_DIV = '13' THEN 'AP'
ELSE 'ETC'
END AS CENT_DIV_FULL
, GRP
, CENT_DIV
FROM TB_DS_PRODUCTY_ADM_DAILY DD
, (SELECT IDNO
, SEQ_NO
, NVL(A.FM_PNUM_01, 0) + NVL(A.FM_PNUM_02, 0) + NVL(A.FM_PNUM_03, 0) + NVL(A.FM_PNUM_04, 0) + NVL(A.FM_PNUM_05, 0) + NVL(A.FM_PNUM_06, 0) + NVL(A.FM_PNUM_07, 0) + NVL(A.FM_PNUM_08, 0)
+ NVL(A.TEMP_PNUM_01, 0) + NVL(A.TEMP_PNUM_02, 0) + NVL(A.TEMP_PNUM_03, 0) + NVL(A.TEMP_PNUM_04, 0) + NVL(A.TEMP_PNUM_05, 0) + NVL(A.TEMP_PNUM_06, 0) + NVL(A.TEMP_PNUM_07, 0) + NVL(A.TEMP_PNUM_08, 0) AS WORKMAN
FROM TB_DS_PRODUCTY_ADM_WORKTIME A) W
, TB_DS_PRODUCTY M
, TB_CM_CD_DTL C
WHERE DD.IDNO = W.IDNO(+)
AND DD.SEQ_NO = W.SEQ_NO(+)
AND DD.IDNO = M.IDNO
AND C.COM_GRP_CD = 'DASH_CENT_DIV'
AND M.CENT_DIV = C.COM_CD(+)
GROUP BY DD.PRD_Y||DD.PRD_M||DD.PRD_D, CENT_DIV, GRP
HAVING (
( GRP = '1' AND CENT_DIV = '2' )--CPG_RDC(상온)
OR ( GRP = '1' AND CENT_DIV = '4' )
OR ( GRP = '5' AND CENT_DIV = '3' )
OR ( GRP = '3' AND CENT_DIV = '7' )
OR ( CENT_DIV IN ('9', '10') )
OR ( CENT_DIV = '1' )
OR ( CENT_DIV = '5' AND GRP = '1' )
OR ( CENT_DIV = '6' AND GRP = '3' )
OR ( CENT_DIV = '8' )
OR ( CENT_DIV = '11' )
OR ( CENT_DIV = '12' )
OR ( CENT_DIV = '13' )
)
ORDER BY CRE_DATE
)
GROUP BY CRE_DATE, CENT_DIV_FULL
) A
PIVOT (
MAX(A.UNLD_PRODUCTY) FOR CRE_DATE IN ('DAY0' AS DAY0, 'DAY1' AS DAY1, 'DAY2' AS DAY2, 'DAY3' AS DAY3, 'DAY4' AS DAY4, 'DAY5' AS DAY5, 'DAY6' AS DAY6, 'DAY7' AS DAY7)
) P
) A
) A
WHERE CATE <![CDATA[ <> ]]> 'M'
ORDER BY CATE, RNUM
너무 어렵꾼,,
728x90
반응형
'개발자 :-) > SQL' 카테고리의 다른 글
| MSSQL PL/SQL 시스템 함수 정리(@@TRANCOUNT, @@ROWCOUNT 등) (0) | 2025.02.03 |
|---|---|
| [MSSQL] mybatis에서 동적쿼리+pivot 사용하기+ 행을 열로 바꾸기 (3) | 2024.11.12 |
| [SQL] 오라클 LAG, LEAD 함수 사용법(이전 행값, 다음 행값 가져오기),전일대비 구하기 (1) | 2023.01.05 |
| [MySQL] 스토어드 프로시저(2) (0) | 2022.10.02 |
| [MySQL] 스토어드 프로시저 (0) | 2022.10.01 |
댓글