WITH all_table AS ( SELECT 'MK' data_type, 'A_KO_D' item_type, '20170501' m_date, 19 c_qt, null a_qt FROM dual UNION ALL SELECT 'MK', 'B_KO_C' , '20170501', null, 81 FROM dual UNION ALL SELECT 'MK', 'B_KO_C' , '20170501', 55, null FROM dual UNION ALL SELECT 'MK', 'A_KO_AWQE', '20170501', null, 353 FROM dual UNION ALL SELECT 'MK', 'A_KO_AWQE', '20170501', 90, null FROM dual UNION ALL SELECT 'MK', 'M_KO_LL' , '20170501', 10, null FROM dual UNION ALL SELECT 'MK', 'MS_KO_CO' , '20170501', null, 19 FROM dual UNION ALL SELECT 'MK', 'MS_KO_CO' , '20170501', 20, null FROM dual UNION ALL SELECT 'MK', 'MX_LV_U' , '20170501', null, 5 FROM dual UNION ALL SELECT 'MK', 'MX_LV_U' , '20170501', 5, null FROM dual ) SELECT * FROM (SELECT data_type , m_date , NVL(item_type, 'TOTAL') item_type , DECODE(g, 0, c_qt, c_qt_x)||'' c_qt , a_qt||'' a_qt , ROUND(LEAST(NVL(a_qt, 0) / DECODE(g, 0, c_qt, c_qt_x), 1) * 100, 2)||'%' 달성률 FROM (SELECT data_type, m_date, item_type , SUM(c_qt) c_qt , SUM(a_qt) a_qt , SUM(c_qt) * SIGN(SUM(a_qt)) c_qt_x , GROUPING(item_type) g FROM all_table WHERE data_type ='MK' AND m_date ='20170501' GROUP BY data_type, m_date, ROLLUP(item_type) ) ) UNPIVOT INCLUDE NULLS (qt FOR gb IN (c_qt, a_qt, 달성률)) ;