with t AS
(
SELECT * FROM (
SELECT REGMONTH, cast(COUNT(DEVAPPNO) AS float) TOTAL, COUNT(MANAGENO) COMPLATE_CNT, ROUND(cast(COUNT(MANAGENO) AS float)/cast(COUNT(DEVAPPNO) AS float)*100) percent
FROM (
SELECT DISTINCT dev.DEVAPPNO,
dev.PLANTCD,
com.MANAGENO,
TO_CHAR(dev.REGDATE, 'MM') REGMONTH,
CASE NVL(com.MANAGENO,'NULL') WHEN 'NULL' THEN '미완료' ELSE '완료' END AS TITLE
FROM DEV_MAIN dev LEFT OUTER JOIN (SELECT * FROM COM_SIGN WHERE TASKSTEP in ('DEVCR01', 'DEVCR02', 'DEVCR03', 'DEVCR04', 'DEVCR05', 'DEVCR06') AND endstatus = 'E') com
ON dev.DEVNO = com.MANAGENO
WHERE TO_CHAR(dev.REGDATE, 'YYYY') = '2014'
AND dev.PLANTCD = '1900'
)
GROUP BY REGMONTH
)
SELECT '발생건', MON1, MON2, MON3, MON4, MON5, MON6, MON7, MON9, MON10, MON11, MON12 FROM T
PIVOT(SUM(TOTAL) for REGMONTH IN('01' as MON1, '02' as MON2, '03' as MON3, '04' as MON4, '05' as MON5, '06' as MON6, '07' as MON7, '08' as MON8, '09' as MON9, '10' as MON10, '11' as MON11, '12' as MON12))
UNION ALL
SELECT '완료건', MON1, MON2, MON3, MON4, MON5, MON6, MON7, MON9, MON10, MON11, MON12 FROM T
PIVOT(SUM(COMPLATE_CNT) for REGMONTH IN('01' as MON1, '02' as MON2, '03' as MON3, '04' as MON4, '05' as MON5, '06' as MON6, '07' as MON7, '08' as MON8, '09' as MON9, '10' as MON10, '11' as MON11, '12' as MON12))
UNION ALL
SELECT '완료율', MON1, MON2, MON3, MON4, MON5, MON6, MON7, MON9, MON10, MON11, MON12 FROM T
PIVOT(SUM(percent) for REGMONTH IN('01' as MON1, '02' as MON2, '03' as MON3, '04' as MON4, '05' as MON5, '06' as MON6, '07' as MON7, '08' as MON8, '09' as MON9, '10' as MON10, '11' as MON11, '12' as MON12))