select 
 SURVEY_YEAR as "SV_YEAR"
,PF_NAME "PREFECTURE"
,case 
   when ki = 1 then '小学校'
   when ki = 2 then '中学校'
   when ki = 3 then '高校'
   when ki = 4 then '短大'
   when ki = 5 then '大学'
   else '大学院'
   end as "KIND"
,sum_kind
from (
-- 小学校
select SURVEY_YEAR
      ,p.PF_CODE 
      ,PF_NAME
      ,1 ki
      ,SUM(ELEMENTARY) sum_kind
from ENROLLMENT_STATUS e
    ,PREFECTURE p
where e.PF_CODE = p.PF_CODE
and   SURVEY_YEAR = '2020'
group by SURVEY_YEAR ,PF_NAME
UNION ALL
-- 中学校
select SURVEY_YEAR
      ,p.PF_CODE 
      ,PF_NAME
      ,2 ki
      ,SUM(MIDDLE) sum_kind
from ENROLLMENT_STATUS e
    ,PREFECTURE p
where e.PF_CODE = p.PF_CODE
and   SURVEY_YEAR = '2020'
group by SURVEY_YEAR ,PF_NAME
UNION ALL
-- 高校
select SURVEY_YEAR
      ,p.PF_CODE 
      ,PF_NAME
      ,3 ki
      ,SUM(HIGH) sum_kind
from ENROLLMENT_STATUS e
    ,PREFECTURE p
where e.PF_CODE = p.PF_CODE
and   SURVEY_YEAR = '2020'
group by SURVEY_YEAR ,PF_NAME
UNION ALL
-- 短大
select SURVEY_YEAR
      ,p.PF_CODE 
      ,PF_NAME
      ,4 ki
      ,SUM(JUNIOR_CLG) sum_kind
from ENROLLMENT_STATUS e
    ,PREFECTURE p
where e.PF_CODE = p.PF_CODE
and   SURVEY_YEAR = '2020'
group by SURVEY_YEAR ,PF_NAME
UNION ALL
-- 大学
select SURVEY_YEAR
      ,p.PF_CODE 
      ,PF_NAME
      ,5 ki
      ,SUM(COLLEGE) sum_kind
from ENROLLMENT_STATUS e
    ,PREFECTURE p
where e.PF_CODE = p.PF_CODE
and   SURVEY_YEAR = '2020'
group by SURVEY_YEAR ,PF_NAME
UNION ALL
-- 大学院
select SURVEY_YEAR
      ,p.PF_CODE 
      ,PF_NAME
      ,6 ki
      ,SUM(GRADUATE) sum_kind
from ENROLLMENT_STATUS e
    ,PREFECTURE p
where e.PF_CODE = p.PF_CODE
and   SURVEY_YEAR = '2020'
group by SURVEY_YEAR ,PF_NAME
)
where sum_kind is not null
order by PF_CODE, ki
;