SELECT NVL(a.root_dept_nm,'계') AS dept_nm
     , SUM(b.cnt) AS "계"
     , SUM(CASE WHEN b.work_grade_cd = '101' THEN b.cnt END) AS pre1
     , SUM(CASE WHEN b.work_grade_cd = '102' THEN b.cnt END) AS pre2
     , SUM(CASE WHEN b.work_grade_cd = '103' THEN b.cnt END) AS pre3
     , SUM(CASE WHEN b.work_grade_cd = '104' THEN b.cnt END) AS pre4
     , SUM(CASE WHEN b.work_grade_cd = '105' THEN b.cnt END) AS pre5
     , SUM(CASE WHEN b.work_grade_cd = '106' THEN b.cnt END) AS pre6
     , SUM(CASE WHEN b.work_grade_cd = '107' THEN b.cnt END) AS pre7
     , SUM(CASE WHEN b.work_grade_cd = '108' THEN b.cnt END) AS pre8
     , SUM(CASE WHEN b.work_grade_cd = '109' THEN b.cnt END) AS pre9
     , SUM(CASE WHEN b.work_grade_cd = '110' THEN b.cnt END) AS pre10
     , SUM(CASE WHEN b.work_grade_cd = '111' THEN b.cnt END) AS pre11
     , SUM(CASE WHEN b.work_grade_cd = '112' THEN b.cnt END) AS pre12
     , SUM(CASE WHEN b.work_grade_cd = '113' THEN b.cnt END) AS pre13
     , SUM(CASE WHEN b.work_grade_cd = '114' THEN b.cnt END) AS pre14
     , SUM(CASE WHEN b.work_grade_cd = '115' THEN b.cnt END) AS pre15
     , SUM(CASE WHEN b.work_grade_cd = '116' THEN b.cnt END) AS pre16
     , SUM(CASE WHEN b.work_grade_cd = '117' THEN b.cnt END) AS pre17
     , SUM(CASE WHEN b.work_grade_cd = '121' THEN b.cnt END) AS pre18
     , SUM(CASE WHEN b.work_grade_cd = '122' THEN b.cnt END) AS pre19
     , SUM(CASE WHEN b.work_grade_cd = '123' THEN b.cnt END) AS pre20
     , SUM(CASE WHEN b.work_grade_cd = '124' THEN b.cnt END) AS pre21
     , SUM(CASE WHEN b.work_grade_cd = '125' THEN b.cnt END) AS pre22
     , SUM(CASE WHEN b.work_grade_cd = '130' THEN b.cnt END) AS pre23
     , SUM(CASE WHEN b.work_grade_cd = '131' THEN b.cnt END) AS pre24
  FROM (SELECT dept_cd
             , CONNECT_BY_ROOT(dept_cd) root_dept_cd
             , CONNECT_BY_ROOT(dept_nm) root_dept_nm
          FROM a_deptcd
         START WITH pdept_cd = '1000000' AND use_yn = 'Y'
        CONNECT BY PRIOR dept_cd = pdept_cd
        ) a
     , (SELECT b.dept_cd
             , b.work_grade_cd
             , COUNT(*) cnt
          FROM a_employee b
             , st_codevalue c
         WHERE b.retire_dt IS NULL
           AND b.work_grade_cd = c.code_value
           AND c.code_cd = 'C116'
           AND c.use_yn  = 'Y'
         GROUP BY b.dept_cd, b.work_grade_cd
        ) b
 WHERE a.deptcd = b.dept_cd(+)
 GROUP BY ROLLUP((a.root_dept_cd, a.root_dept_nm))
 ORDER BY GROUPING(a.root_dept_cd) DESC, a.root_dept_cd