|
급하게 작성해서 오류가 있을 수 있으나 수정하셔서 활용은 가능하실 것 같네요.
WITH temp AS
(SELECT '199901' reg_dd, 100 a FROM DUAL
UNION ALL
SELECT '199902' reg_dd, 200 a FROM DUAL
UNION ALL
SELECT '200001' reg_dd, 500 a FROM DUAL
UNION ALL
SELECT '200002' reg_dd, 250 a FROM DUAL
UNION ALL
SELECT '200002' reg_dd, 200 a FROM DUAL
UNION ALL
SELECT '200003' reg_dd, 200 a FROM DUAL
UNION ALL
SELECT '200301' reg_dd, 1 a FROM DUAL
UNION ALL
SELECT '200401' reg_dd, 500 a FROM DUAL
UNION ALL
SELECT '200501' reg_dd, 100 a FROM DUAL
UNION ALL
SELECT '200601' reg_dd, 200 a FROM DUAL
UNION ALL
SELECT '200701' reg_dd, 450 a FROM DUAL
UNION ALL
SELECT '200801' reg_dd, 320 a FROM DUAL
UNION ALL
SELECT '200901' reg_dd, 120 a FROM DUAL
UNION ALL
SELECT '201001' reg_dd, 220 a FROM DUAL
UNION ALL
SELECT '201101' reg_dd, 520 a FROM DUAL)
SELECT DECODE (NO
,1, '0~200 cnt'
,2, '0~200 sum'
,3, '0~200 (%)'
,4, '200~400 cnt'
,5, '200~400 sum'
,6, '200~400 (%)'
,7, '400~600 cnt'
,8, '400~600 sum'
,9, '400~600 (%)')
gubun
,MIN (
DECODE (reg_yy || no
,'20021', c1_cnt
,'20022', c1_sum
,'20023', ROUND (c1_per_sum)
,'20024', c2_cnt
,'20025', c2_sum
,'20026', ROUND (c2_per_sum)
,'20027', c3_cnt
,'20028', c3_sum
,'20029', ROUND (c3_per_sum)))
AS "Y2002"
,MIN (
DECODE (reg_yy || no
,'20031', c1_cnt
,'20032', c1_sum
,'20033', ROUND (c1_per_sum)
,'20034', c2_cnt
,'20035', c2_sum
,'20036', ROUND (c2_per_sum)
,'20037', c3_cnt
,'20038', c3_sum
,'20039', ROUND (c3_per_sum)))
AS "Y2003"
,MIN (
DECODE (reg_yy || no
,'20041', c1_cnt
,'20042', c1_sum
,'20043', ROUND (c1_per_sum)
,'20044', c2_cnt
,'20045', c2_sum
,'20046', ROUND (c2_per_sum)
,'20047', c3_cnt
,'20048', c3_sum
,'20049', ROUND (c3_per_sum)))
AS "Y2004"
,MIN (
DECODE (reg_yy || no
,'20051', c1_cnt
,'20052', c1_sum
,'20053', ROUND (c1_per_sum)
,'20054', c2_cnt
,'20055', c2_sum
,'20056', ROUND (c2_per_sum)
,'20057', c3_cnt
,'20058', c3_sum
,'20059', ROUND (c3_per_sum)))
AS "Y2005"
,MIN (
DECODE (reg_yy || no
,'20061', c1_cnt
,'20062', c1_sum
,'20063', ROUND (c1_per_sum)
,'20064', c2_cnt
,'20065', c2_sum
,'20066', ROUND (c2_per_sum)
,'20067', c3_cnt
,'20068', c3_sum
,'20069', ROUND (c3_per_sum)))
AS "Y2006"
,MIN (
DECODE (reg_yy || no
,'20071', c1_cnt
,'20072', c1_sum
,'20073', ROUND (c1_per_sum)
,'20074', c2_cnt
,'20075', c2_sum
,'20076', ROUND (c2_per_sum)
,'20077', c3_cnt
,'20078', c3_sum
,'20079', ROUND (c3_per_sum)))
AS "Y2007"
,MIN (
DECODE (reg_yy || no
,'20081', c1_cnt
,'20082', c1_sum
,'20083', ROUND (c1_per_sum)
,'20084', c2_cnt
,'20085', c2_sum
,'20086', ROUND (c2_per_sum)
,'20087', c3_cnt
,'20088', c3_sum
,'20089', ROUND (c3_per_sum)))
AS "Y2008"
,MIN (
DECODE (reg_yy || no
,'20091', c1_cnt
,'20092', c1_sum
,'20093', ROUND (c1_per_sum)
,'20094', c2_cnt
,'20095', c2_sum
,'20096', ROUND (c2_per_sum)
,'20097', c3_cnt
,'20098', c3_sum
,'20099', ROUND (ROUND (c3_per_sum))))
AS "Y2009"
,MIN (
DECODE (reg_yy || no
,'20101', c1_cnt
,'20102', c1_sum
,'20103', ROUND (c1_per_sum)
,'20104', c2_cnt
,'20105', c2_sum
,'20106', ROUND (c2_per_sum)
,'20107', c3_cnt
,'20108', c3_sum
,'20109', ROUND (c3_per_sum)))
AS "Y2010"
,MIN (
DECODE (reg_yy || no
,'20111', c1_cnt
,'20112', c1_sum
,'20113', ROUND (c1_per_sum)
,'20114', c2_cnt
,'20115', c2_sum
,'20116', ROUND (c2_per_sum)
,'20117', c3_cnt
,'20118', c3_sum
,'20119', ROUND (c3_per_sum)))
AS "Y2011"
FROM (SELECT reg_yy
,MAX (c1_cnt) AS c1_cnt
,MAX (c2_cnt) AS c2_cnt
,MAX (c3_cnt) AS c3_cnt
,MAX (c1_sum) AS c1_sum
,MAX (c2_sum) AS c2_sum
,MAX (c3_sum) AS c3_sum
,MAX (c1_sum / c1_per_sum) * 100 AS c1_per_sum
,MAX (c2_sum / c2_per_sum) * 100 AS c2_per_sum
,MAX (c3_sum / c3_per_sum) * 100 AS c3_per_sum
FROM (SELECT reg_yy
,c1
,c2
,c3
-- cnt
,COUNT (c1) OVER (ORDER BY reg_dd) c1_cnt
,COUNT (c2) OVER (ORDER BY reg_dd) c2_cnt
,COUNT (c3) OVER (ORDER BY reg_dd) c3_cnt
-- sum
,SUM (c1) OVER (ORDER BY reg_dd) c1_sum
,SUM (c2) OVER (ORDER BY reg_dd) c2_sum
,SUM (c3) OVER (ORDER BY reg_dd) c3_sum
-- all_sum
,SUM (c1) OVER () c1_per_sum
,SUM (c2) OVER () c2_per_sum
,SUM (c3) OVER () c3_per_sum
FROM (SELECT (CASE WHEN reg_dd <= '200212' THEN '2002' ELSE SUBSTR (reg_dd, 1, 4) END) reg_yy
,reg_dd
, (CASE WHEN a BETWEEN 0 AND 200 THEN a END) c1 -- '0~200'
, (CASE WHEN a BETWEEN 201 AND 400 THEN a END) c2 -- '201~400'
, (CASE WHEN a BETWEEN 401 AND 600 THEN a END) c3 -- '401~600'
FROM temp))
GROUP BY reg_yy)
,(SELECT LEVEL no
FROM DUAL
CONNECT BY LEVEL <= 9)
GROUP BY no
|
