데이터베이스 질문과 답변
안녕하세요..pivot 시 합계나 소계를 표시하기 위해 pivot후 다음과 같이 다시 rollup했는데요..
다른 방법도 있지 않을까 해서 문의 드립니다.
WITH TB_A AS (
SELECT '01' CD, '01코드명' NM FROM DUAL UNION ALL
SELECT '02' CD, '02코드명' NM FROM DUAL UNION ALL
SELECT '03' CD, '03코드명' NM FROM DUAL
)
, TB_B AS (
SELECT '01' CD, '20240601' DY FROM DUAL UNION ALL
SELECT '01' CD, '20240620' DY FROM DUAL UNION ALL
SELECT '03' CD, '20240501' DY FROM DUAL UNION ALL
SELECT '01' CD, '20240510' DY FROM DUAL
)
, TB_C AS (
SELECT A.CD
, A.NM
, TO_CHAR(TO_DATE(B.DY, 'YYYYMMDD'), 'YYYYMM') DY
, SUM(DECODE(B.DY, NULL, 0, 1)) CNT
FROM TB_A A
, TB_B B
WHERE A.CD = B.CD(+)
GROUP BY A.CD, A.NM, B.DY
ORDER BY A.CD, B.DY
)
, TB_D AS (
SELECT * FROM TB_C
PIVOT (SUM(CNT) FOR DY IN ('202405' AS TC_202405, '202406' AS TC_202406))
)
SELECT CD
, DECODE(NM, NULL, '<합계>', NM) NM
, SUM(TC_202405) TC_202405
, SUM(TC_202406) TC_202406
FROM TB_D
GROUP BY ROLLUP(CD, NM)
HAVING (GROUPING(CD) = 0 AND GROUPING(NM) = 0)
OR (GROUPING(CD) = 1 AND GROUPING(NM) = 1)
;
혹시 pivot전에 합계를 구하고 pivot하는 방법이나..다른 방법이 있으시면 조언 부탁드립니다.
감사합니다..
by 뉴비디비
[2024.06.22 00:06:22]
-- 이렇게 해도 될꺼 같아요. SELECT TB_A.CD , DECODE(TB_A.CD, NULL, '<합계>', MIN(TB_A.NM)) AS NM , SUM( CASE WHEN TO_CHAR(TO_DATE(TB_B.DY, 'YYYYMMDD'), 'YYYYMM') = '202405' THEN 1 ELSE NULL END ) AS TC_202405 , SUM( CASE WHEN TO_CHAR(TO_DATE(TB_B.DY, 'YYYYMMDD'), 'YYYYMM') = '202406' THEN 1 ELSE NULL END ) AS TC_202406 FROM TB_A LEFT JOIN TB_B ON TB_A.CD = TB_B.CD GROUP BY ROLLUP(TB_A.CD) ORDER BY TB_A.CD ;
by 동희
[2024.06.24 08:35:30]
답변 감사드립니다...
기간만큼 동적으로 칼럼으로 표시해야 한다는 생각에 피벗에 꼿혀 있었는데 굳이 피벗을 쓸 필요는 없었네요...
감사합니다...
by 마농
[2024.06.24 08:29:36]
SELECT a.cd
, NVL(a.nm, '<합계>') nm
, COUNT(DECODE(SUBSTR(b.dy, 1, 6), '202405', 1)) tc_202405
, COUNT(DECODE(SUBSTR(b.dy, 1, 6), '202406', 1)) tc_202406
FROM tb_a a
, tb_b b
WHERE a.cd = b.cd(+)
AND SUBSTR(b.dy(+), 1, 6) IN ('202405', '202406')
GROUP BY ROLLUP((a.cd, a.nm))
;
by 동희
[2024.06.24 08:36:06]
마농님 답변 감사드립니다....
피벗을 쓸 필요까지는 없었네요..ㅠㅠ
그런데 만약 피벗을 써야 한다면 피벗 후 다시 rollup 하는 방법 밖에는 없는건가요?
by 마농
[2024.06.24 13:07:13]
피벗 후 롤업을 하든, 롤업 후 피벗을 하든, 다양한 시도가 있을 수는 있겠지요.
하지만 좀 억지스러울 것 같습니다.
피벗의 문법은 정해진 틀에 끼워 맞춰야 하는 딱딱한 문법입니다.
고전적인 SUM(DECODE 방식이 오히려 틀에 억메이지 않고 자유로울 수 있습니다.
그때 그때 상황에 맞게 적절한 선택을 하시면 됩니다.
-- 롤업 후 피벗 --
SELECT *
FROM (SELECT a.cd
, NVL(a.nm, '<합계>') nm
, SUBSTR(b.dy, 1, 6) ym
, COUNT(b.dy) cnt
FROM tb_a a
LEFT OUTER JOIN tb_b b
ON a.cd = b.cd
AND SUBSTR(b.dy, 1, 6) IN ('202405', '202406')
GROUP BY SUBSTR(b.dy, 1, 6), ROLLUP((a.cd, a.nm))
)
PIVOT (SUM(cnt) FOR ym IN ('202405' tc_202405, '202406' tc_202406))
ORDER BY cd
;
-- 피벗 후 롤업 --
SELECT cd
, NVL(nm, '<합계>') nm
, SUM(tc_202405) tc_202405
, SUM(tc_202406) tc_202406
FROM (SELECT a.cd
, a.nm
, SUBSTR(b.dy, 1, 6) ym
FROM tb_a a
LEFT OUTER JOIN tb_b b
ON a.cd = b.cd
AND SUBSTR(b.dy, 1, 6) IN ('202405', '202406')
)
PIVOT (COUNT(ym) FOR ym IN ('202405' tc_202405, '202406' tc_202406))
GROUP BY ROLLUP((cd, nm))
;
by 동희
[2024.06.25 08:43:42]
답변 감사드립니다..^^
) 버튼을 클릭하여 작성 하시면 됩니다.