데이터베이스 질문과 답변
by 푸릉이 [2018.02.28 08:51:19]
늘 도움받으면서 공부하고 있습니다.
WITH T AS
(
SELECT '0100' CODE, '0' CNT1, '2' AS CNT2 FROM DUAL UNION ALL
SELECT '0450' CODE, '1' CNT1, '0' AS CNT2 FROM DUAL UNION ALL
SELECT '0450' CODE, '0' CNT1, '0' AS CNT2 FROM DUAL UNION ALL
SELECT '0400' CODE, '0' CNT1, '0' AS CNT2 FROM DUAL UNION ALL
SELECT '0205' CODE, '0' CNT1, '0' AS CNT2 FROM DUAL UNION ALL
SELECT '0550' CODE, '0' CNT1, '0' AS CNT2 FROM DUAL UNION ALL
SELECT '4510' CODE, '0' CNT1, '0' AS CNT2 FROM DUAL
)
SELECT * FROM T
이런 데이터가 있는데요
0100 0450 0400 0205
cnt1 x x x
cnt2 x x
이런식으로 코드값에 해당하는 건수합산을 피벗으로 cnt1, cnt2로 구하고 싶습니다.
그리고 혹시 프로시져말고 저 코드값 종류가 50개정도 있는데 FOR CODE IN ('0010' AS "선물1", '0020' AS "선물2", ............)
0010' AS "선물1", '0020' AS "선물2", ........... 이부분을 코드테이블에서 select 로 가져오는 신통방통한 방법은 없을까요?
-- 샘플데이터에 450이 두개라 451로 바꿨습니다.
WITH T AS
(
SELECT '0100' CODE, '0' CNT1, '2' AS CNT2 FROM DUAL UNION ALL
SELECT '0450' CODE, '1' CNT1, '0' AS CNT2 FROM DUAL UNION ALL
SELECT '0451' CODE, '0' CNT1, '0' AS CNT2 FROM DUAL UNION ALL
SELECT '0400' CODE, '0' CNT1, '0' AS CNT2 FROM DUAL UNION ALL
SELECT '0205' CODE, '0' CNT1, '0' AS CNT2 FROM DUAL UNION ALL
SELECT '0550' CODE, '0' CNT1, '0' AS CNT2 FROM DUAL UNION ALL
SELECT '4510' CODE, '0' CNT1, '0' AS CNT2 FROM DUAL
)
SELECT *
FROM (SELECT CODE , CNT1 AS CNT , 'CNT1' GB FROM T
UNION ALL
SELECT CODE , CNT2 , 'CNT2' GB FROM T )
PIVOT ( MIN(CNT) FOR (CODE) IN ('0100','0450','0451','0400','0205','0550','4510')); OR (CODE) IN ('0100','0450','0451','0400','0205','0550','4510'));
총계 0100 0450 0400 0205
cnt1 x x x x
cnt2 x x x
총계까지 추가하려면 어떻게 해야하나요?
50개 고정이라면? 50개 다 적어주시면 됩니다.
가변적으로 적용하는 방법은 SQL에는 없습니다.
프로그램에서 동적 SQL 적용하셔야 합니다.
SELECT *
FROM t
UNPIVOT (cnt FOR gb IN (cnt1, cnt2))
PIVOT (SUM(cnt) FOR code IN ( '0100' cd1
, '0450' cd2
, '0400' cd3
, '0205' cd4
, '0550' cd5
, '4510' cd6
) )
;
총계 0100 0450 0400 0205
cnt1 x x x x
cnt2 x x x
총계까지 추가하려면 어떻게 해야하나요?
SELECT gb
, SUM(cnt) tot
, SUM(DECODE(code, '0100', cnt)) cd1
, SUM(DECODE(code, '0450', cnt)) cd2
, SUM(DECODE(code, '0400', cnt)) cd3
, SUM(DECODE(code, '0205', cnt)) cd4
, SUM(DECODE(code, '0550', cnt)) cd5
, SUM(DECODE(code, '4510', cnt)) cd6
FROM t
UNPIVOT (cnt FOR gb IN (cnt1, cnt2))
GROUP BY gb
ORDER BY gb
;
SELECT *
FROM (SELECT NVL(code, '9999') code
, SUM(cnt1) cnt1
, SUM(cnt2) cnt2
FROM t
GROUP BY ROLLUP(code)
)
UNPIVOT (cnt FOR gb IN (cnt1, cnt2))
PIVOT (SUM(cnt) FOR code IN ( '9999' tot
, '0100' cd1
, '0450' cd2
, '0400' cd3
, '0205' cd4
, '0550' cd5
, '4510' cd6
) )
ORDER BY gb
;
WITH T AS
(
SELECT '2016' YEAR, '0100' CODE, '0' CNT1, '2' AS CNT2 FROM DUAL UNION ALL
SELECT '2016', '0450' CODE, '1' CNT1, '0' AS CNT2 FROM DUAL UNION ALL
SELECT '2016', '0450' CODE, '0' CNT1, '0' AS CNT2 FROM DUAL UNION ALL
SELECT '2017', '0400' CODE, '3' CNT1, '0' AS CNT2 FROM DUAL UNION ALL
SELECT '2017', '0205' CODE, '0' CNT1, '0' AS CNT2 FROM DUAL UNION ALL
SELECT '2017', '0550' CODE, '0' CNT1, '4' AS CNT2 FROM DUAL UNION ALL
SELECT '2017', '4510' CODE, '0' CNT1, '0' AS CNT2 FROM DUAL
)
년도가 추가되면 어떻게 해야하죠?
최종적으로 산출하고 싶은 형태가
'0100' ...........
total x x x x
Total cnt1 x x x x
cnt2 x x x x
2016 cnt1 x x x x
cnt1 x x x x
2017 cnt1 x x x x
cnt2 x x x x
이런 형태입니다.
결과표가 정확한건가요? 뭔가 이상한데요?
컬럼 타이틀도 정확하게 채워 주시고.
결과표 숫자를 예시 자료 기준으로 정확하게 채워주세요.
아 죄송합니다. 수치는 t 테이블과 다르지만 이런식입니다.
total 0100 0450 0400 ....
total 30 10 10 10
Total cnt1 12 4 4 4
cnt2 18 6 6 6
2016 cnt1 3 1 1 1
cnt1 6 2 2 2
2017 cnt1 9 3 3 3
cnt2 12 4 4 4
편집기에서는 줄이 맞게 보이는데 저장하니까 약간 나르게 나오네요.
SELECT NVL(year, 'Total') yesr
, NVL(gb , 'total') gb
, SUM(cnt) total
, SUM(DECODE(code, '0100', cnt)) "0100"
, SUM(DECODE(code, '0450', cnt)) "0450"
, SUM(DECODE(code, '0400', cnt)) "0400"
, SUM(DECODE(code, '0205', cnt)) "0205"
, SUM(DECODE(code, '0550', cnt)) "0550"
, SUM(DECODE(code, '4510', cnt)) "4510"
FROM t
UNPIVOT (cnt FOR gb IN (cnt1, cnt2)) a
-- GROUP BY CUBE(gb, year)
-- HAVING GROUPING_ID(year, gb) != 1
GROUP BY ROLLUP(gb, year)
ORDER BY a.year NULLS FIRST
, a.gb NULLS FIRST
;
경이롭습니다. ^^
CUBE 괜히 썼네요.
ROLLUP 하면 HAVING 필요 없습니다.
GROUP BY ROLLUP(gb, year)
) 버튼을 클릭하여 작성 하시면 됩니다.