WITH tmp AS ( SELECT SUBSTR(a.trainymd, 1, 6) ym , a.empno , SUBSTR(a.trainymd, 7, 2)+0 dd , a.week v0 -- 주차 , MIN(DECODE(a.traingb, '03', a.traindept)) v1 -- 배치부서 , MIN(DECODE(a.traingb, '03', a.traingbcd)) v2 -- 근무형태 , MIN(DECODE(a.traingb, '05', a.traindept)) v3 -- 당직위치 , MIN(DECODE(a.traingb, '05', a.traingbcd)) v4 -- 당직형태 , MIN(DECODE(a.traingb, '03', b.duttm))||'' v5 -- 연속수련 , GREATEST(NVL(ROUND( ( TO_DATE(a.trainymd||MIN(DECODE(a.traingb, '03', b.strtime)), 'yyyymmddhh24mi') - LAG( TO_DATE(MIN(DECODE(a.traingb, '03', CASE WHEN b.endtime = '2400' THEN TO_CHAR(TO_DATE(a.trainymd, 'yyyymmdd') + 1, 'yyyymmdd')||'0000' WHEN b.endtime <= b.strtime THEN TO_CHAR(TO_DATE(a.trainymd, 'yyyymmdd') + 1, 'yyyymmdd')||b.endtime ELSE a.trainymd||b.endtime END ) ), 'yyyymmddhh24mi') ) OVER(PARTITION BY a.empno ORDER BY a.trainymd) ) * 24, 2), 0), 0)||'' v6 -- 휴식시간 , SUM(MIN(DECODE(a.traingb, '03', b.duttm))) OVER(PARTITION BY a.empno, a.week ORDER BY a.trainymd)||'' v7 -- 누적시간 , COUNT(MIN(DECODE(a.traingb, '05', 1))) OVER(PARTITION BY a.empno, a.week ORDER BY a.trainymd)||'' v8 -- 누적당직 , MIN(DECODE(c.clngb, '', '0', '1')) v9 -- 주당휴일 FROM er_resitrainestdd a , er_resiweektime b , er_calendar c WHERE a.ocpgrdcd = :v_ocpgrdcd AND a.trainymd >= TO_CHAR( TRUNC(TO_DATE(:v_ym, 'yyyymm'), 'iw'), 'yyyymmdd') AND a.trainymd <= TO_CHAR(LAST_DAY(TO_DATE(:v_ym, 'yyyymm')) , 'yyyymmdd') AND b.trainym = SUBSTR(a.trainymd, 1, 6) AND b.traindept = a.traindept AND b.traingbcd = a.traingbcd AND a.trainymd = c.ymd GROUP BY a.empno, a.trainymd, a.week ) SELECT * FROM (SELECT * FROM tmp WHERE ym = :v_ym ) UNPIVOT (v FOR gb IN (v0, v1, v2, v3, v4, v5, v6, v7, v8, v9)) PIVOT (MIN(v) FOR dd IN ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 , 11, 12, 13, 14, 15, 16, 17, 18, 19, 20 , 21, 22, 23, 24, 25, 26, 27, 28, 29, 30 , 31 )) ;