WITH t AS
(
SELECT 1000 Oper, 'de1' Device, 'no1' No, 'n' Hold, 'y' Rework, 100 Qty FROM dual
UNION ALL SELECT 1000, 'de1', 'no2', 'y', 'n', 100 FROM dual
UNION ALL SELECT 1000, 'de1', 'no3', 'n', 'n', 100 FROM dual
UNION ALL SELECT 1000, 'de3', 'no4', 'y', 'y', 100 FROM dual
)
SELECT CASE WHEN grp = '11'
            THEN DECODE(gid,0,'TOTAL',1,'NOMAL',2,'REWORK',3,'HOLD')
            ELSE TO_CHAR(oper)
        END oper
     , CASE WHEN grp = '01'
            THEN DECODE(gid,0,'TOTAL',1,'NOMAL',2,'REWORK',3,'HOLD')
            ELSE device
        END device
     , CASE WHEN grp IN ('01','11')
            THEN TO_CHAR(DECODE(gid,0,cnt_t,1,cnt_n,2,cnt_r,3,cnt_h))
            ELSE no
        END no
     , hold
     , rework
     , DECODE(gid,0,sum_t,1,sum_n,2,sum_r,3,sum_h) qty
  FROM (SELECT oper
             , device
             , no
             , hold
             , rework
             , SUM(DECODE(hold,'y',qty)) sum_h
             , SUM(DECODE(rework,'y',qty)) sum_r
             , SUM(DECODE(hold||rework,'nn',qty)) sum_n
             , SUM(qty) sum_t
             , COUNT(DECODE(hold,'y',1)) cnt_h
             , COUNT(DECODE(rework,'y',1)) cnt_r
             , COUNT(DECODE(hold||rework,'nn',1)) cnt_n
             , COUNT(*) cnt_t
             , GROUP_ID() gid
             , GROUPING(device)||GROUPING(no) grp
          FROM t
         WHERE oper = 1000
         GROUP BY oper, ROLLUP(oper, oper, oper
                               , device, device, device, device
                               , (no, hold, rework)
                               )
         ORDER BY oper, device, no, gid DESC
        )
;