--원본 데이터
SELECT * FROM test
--결과 데이터
--통계 처리한 데이터
SELECT
DECODE(SUBSTR(regdate,1,4),NULL,' 총계',SUBSTR(regdate,1,4)) yyyy,
DECODE(prpos,NULL,'소계',prposname) AS prposname,
DECODE(prpossort,NULL,99999,prpossort) AS prpossort,
prpos,
SUM(DECODE(SUBSTR(regdate,6,2),'01',1,null)) AS "1",
SUM(DECODE(SUBSTR(regdate,6,2),'02',1,null)) AS "2",
SUM(DECODE(SUBSTR(regdate,6,2),'03',1,null)) AS "3",
SUM(DECODE(SUBSTR(regdate,6,2),'04',1,null)) AS "4",
SUM(DECODE(SUBSTR(regdate,6,2),'05',1,null)) AS "5",
SUM(DECODE(SUBSTR(regdate,6,2),'06',1,null)) AS "6",
SUM(DECODE(SUBSTR(regdate,6,2),'07',1,null)) AS "7",
SUM(DECODE(SUBSTR(regdate,6,2),'08',1,null)) AS "8",
SUM(DECODE(SUBSTR(regdate,6,2),'09',1,null)) AS "9",
SUM(DECODE(SUBSTR(regdate,6,2),'10',1,null)) AS "10",
SUM(DECODE(SUBSTR(regdate,6,2),'11',1,null)) AS "11",
SUM(DECODE(SUBSTR(regdate,6,2),'12',1,null)) AS "12",
COUNT(hanoknum) AS total
FROM (
SELECT * FROM test
)
GROUP BY CUBE(SUBSTR(regdate,1,4),prpos)
ORDER BY yyyy DESC, prpossort ASC