꼭 rollup으로 해야하나요?

WITH T AS (
 SELECT 'MODEL01' MODEL, 'LINE01' LINE, 'MCODE01' MCODE, 'SCODE01' SCODE, 1 VAL1, 0 VAL2, 0 VAL3, 0 VAL4, 0 VAL5 FROM DUAL UNION ALL
 SELECT 'MODEL01' MODEL, 'LINE01' LINE, 'MCODE01' MCODE, 'SCODE02' SCODE, 2 VAL1, 0 VAL2, 0 VAL3, 0 VAL4, 0 VAL5 FROM DUAL UNION ALL
 SELECT 'MODEL01' MODEL, 'LINE01' LINE, 'MCODE02' MCODE, 'SCODE03' SCODE, 3 VAL1, 0 VAL2, 0 VAL3, 0 VAL4, 0 VAL5 FROM DUAL UNION ALL
 SELECT 'MODEL01' MODEL, 'LINE01' LINE, 'MCODE02' MCODE, 'SCODE04' SCODE, 4 VAL1, 0 VAL2, 0 VAL3, 0 VAL4, 0 VAL5 FROM DUAL UNION ALL
 SELECT 'MODEL01' MODEL, 'LINE02' LINE, 'MCODE01' MCODE, 'SCODE01' SCODE, 11 VAL1, 0 VAL2, 0 VAL3, 0 VAL4, 0 VAL5 FROM DUAL UNION ALL
 SELECT 'MODEL01' MODEL, 'LINE02' LINE, 'MCODE01' MCODE, 'SCODE02' SCODE, 22 VAL1, 0 VAL2, 0 VAL3, 0 VAL4, 0 VAL5 FROM DUAL UNION ALL
 SELECT 'MODEL01' MODEL, 'LINE02' LINE, 'MCODE02' MCODE, 'SCODE03' SCODE, 33 VAL1, 0 VAL2, 0 VAL3, 0 VAL4, 0 VAL5 FROM DUAL UNION ALL
 SELECT 'MODEL01' MODEL, 'LINE02' LINE, 'MCODE02' MCODE, 'SCODE04' SCODE, 44 VAL1, 0 VAL2, 0 VAL3, 0 VAL4, 0 VAL5 FROM DUAL UNION ALL
 SELECT 'MODEL01' MODEL, 'LINE03' LINE, 'MCODE01' MCODE, 'SCODE01' SCODE, 3 VAL1, 0 VAL2, 0 VAL3, 0 VAL4, 0 VAL5 FROM DUAL UNION ALL
 SELECT 'MODEL01' MODEL, 'LINE03' LINE, 'MCODE01' MCODE, 'SCODE02' SCODE, 6 VAL1, 0 VAL2, 0 VAL3, 0 VAL4, 0 VAL5 FROM DUAL UNION ALL
 SELECT 'MODEL01' MODEL, 'LINE03' LINE, 'MCODE02' MCODE, 'SCODE03' SCODE, 9 VAL1, 0 VAL2, 0 VAL3, 0 VAL4, 0 VAL5 FROM DUAL UNION ALL
 SELECT 'MODEL01' MODEL, 'LINE03' LINE, 'MCODE02' MCODE, 'SCODE04' SCODE, 4 VAL1, 0 VAL2, 0 VAL3, 0 VAL4, 0 VAL5 FROM DUAL UNION ALL
 SELECT 'MODEL02' MODEL, 'LINE01' LINE, 'MCODE01' MCODE, 'SCODE01' SCODE, 33 VAL1, 0 VAL2, 0 VAL3, 0 VAL4, 0 VAL5 FROM DUAL UNION ALL
 SELECT 'MODEL02' MODEL, 'LINE01' LINE, 'MCODE01' MCODE, 'SCODE02' SCODE, 66 VAL1, 0 VAL2, 0 VAL3, 0 VAL4, 0 VAL5 FROM DUAL UNION ALL
 SELECT 'MODEL02' MODEL, 'LINE01' LINE, 'MCODE02' MCODE, 'SCODE03' SCODE, 99 VAL1, 0 VAL2, 0 VAL3, 0 VAL4, 0 VAL5 FROM DUAL UNION ALL
 SELECT 'MODEL02' MODEL, 'LINE01' LINE, 'MCODE02' MCODE, 'SCODE04' SCODE, 55 VAL1, 0 VAL2, 0 VAL3, 0 VAL4, 0 VAL5 FROM DUAL UNION ALL
 SELECT 'MODEL03' MODEL, 'LINE01' LINE, 'MCODE01' MCODE, 'SCODE01' SCODE, 7 VAL1, 0 VAL2, 0 VAL3, 0 VAL4, 0 VAL5 FROM DUAL UNION ALL
 SELECT 'MODEL03' MODEL, 'LINE01' LINE, 'MCODE01' MCODE, 'SCODE02' SCODE, 8 VAL1, 0 VAL2, 0 VAL3, 0 VAL4, 0 VAL5 FROM DUAL UNION ALL
 SELECT 'MODEL03' MODEL, 'LINE01' LINE, 'MCODE02' MCODE, 'SCODE03' SCODE, 5 VAL1, 0 VAL2, 0 VAL3, 0 VAL4, 0 VAL5 FROM DUAL UNION ALL
 SELECT 'MODEL03' MODEL, 'LINE01' LINE, 'MCODE02' MCODE, 'SCODE04' SCODE, 1 VAL1, 0 VAL2, 0 VAL3, 0 VAL4, 0 VAL5 FROM DUAL
 )
SELECT NVL(MODEL,'총계') MODELCD
     , NVL(LINE,NVL2(MODEL,'소계','총계')) LINECD 
     , NVL(MCODE,NVL2(MODEL,'소계','총계')) MCODECD
     , SCODE, SUM(VAL1) VAL1, SUM(VAL2) VAL2, SUM(VAL3) VAL3, SUM(VAL4) VAL4, SUM(VAL5) VAL5 
  FROM T
 GROUP BY GROUPING SETS(SCODE, (MODEL, SCODE),(MODEL, LINE, MCODE, SCODE))
 ORDER BY MODEL NULLS FIRST, LINE NULLS FIRST, MCODE, SCODE

SELECT NVL(MODEL,'총계') MODELCD
, NVL(LINE,NVL2(MODEL,'소계','총계')) LINECD 
, NVL(MCODE,NVL2(MODEL,'소계','총계')) MCODECD
, SCODE, SUM(VAL1) VAL1, SUM(VAL2) VAL2, SUM(VAL3) VAL3, SUM(VAL4) VAL4, SUM(VAL5) VAL5
FROM T
GROUP BY ROLLUP(SCODE, MODEL, (LINE, MCODE))
HAVING GROUPING_ID(MODEL, LINE, MCODE, SCODE) != 15 
ORDER BY MODEL NULLS FIRST, LINE NULLS FIRST, MCODE, SCODE

아.. 막연히 어렵게 생각했네요. rollup이 더 쉽네요;

롤업 대상과 비대상 항목을 구별할 줄 알아야 하구요. scode 는 롤업에서 제외해야 하구요.
롤업 항목중 괄호로 묶어줘야 할 항목이 있는지 파악하셔야 합니다. (line, mcode)
마지막으로 출력 순서와, 출력값 조정 등을 잘 하시면 됩니다.

SELECT DECODE(1, GROUPING(model), '총계', model) model
     , DECODE(1, GROUPING(model), '총계', GROUPING(line), '소계', line) line
     , DECODE(1, GROUPING(model), '총계', GROUPING(line), '소계', mcode) mcode
     , scode
     , SUM(val1) val1
     , SUM(val2) val2
     , SUM(val3) val3
     , SUM(val4) val4
     , SUM(val5) val5
     , GROUPING(model)
  FROM t a
 GROUP BY ROLLUP(model, (line, mcode)), scode
 ORDER BY a.model NULLS FIRST
        , a.line  NULLS FIRST
        , a.mcode NULLS FIRST
        , a.scode
;