select SV_YEAR
     , PREFECTURE
     , KIND
     , AMT
  from (
select SURVEY_YEAR as SV_YEAR
     , ENROLLMENT_STATUS.PF_CODE
     , PF_NAME as PREFECTURE
     , '小学校' as KIND
     , sum(ELEMENTARY) as AMT
 from ENROLLMENT_STATUS
   inner join PREFECTURE
     on ENROLLMENT_STATUS.PF_CODE = PREFECTURE.PF_CODE
where SURVEY_YEAR = 2020
  and ELEMENTARY is not null
group by SURVEY_YEAR, PF_NAME
union all
select SURVEY_YEAR as SV_YEAR
     , ENROLLMENT_STATUS.PF_CODE
     , PF_NAME as PREFECTURE
     , '中学校' as KIND
     , sum(MIDDLE) as AMT
 from ENROLLMENT_STATUS
   inner join PREFECTURE
     on ENROLLMENT_STATUS.PF_CODE = PREFECTURE.PF_CODE
where SURVEY_YEAR = 2020
  and MIDDLE is not null
group by SURVEY_YEAR, PF_NAME
union all
select SURVEY_YEAR as SV_YEAR
     , ENROLLMENT_STATUS.PF_CODE
     , PF_NAME as PREFECTURE
     , '高校' as KIND
     , sum(HIGH) as AMT
 from ENROLLMENT_STATUS
   inner join PREFECTURE
     on ENROLLMENT_STATUS.PF_CODE = PREFECTURE.PF_CODE
where SURVEY_YEAR = 2020
  and HIGH is not null
group by SURVEY_YEAR, PF_NAME
union all
select SURVEY_YEAR as SV_YEAR
     , ENROLLMENT_STATUS.PF_CODE
     , PF_NAME as PREFECTURE
     , '短大' as KIND
     , sum(JUNIOR_CLG) as AMT
 from ENROLLMENT_STATUS
   inner join PREFECTURE
     on ENROLLMENT_STATUS.PF_CODE = PREFECTURE.PF_CODE
where SURVEY_YEAR = 2020
  and JUNIOR_CLG is not null
group by SURVEY_YEAR, PF_NAME
union all
select SURVEY_YEAR as SV_YEAR
     , ENROLLMENT_STATUS.PF_CODE
     , PF_NAME as PREFECTURE
     , '大学' as KIND
     , sum(COLLEGE) as AMT
 from ENROLLMENT_STATUS
   inner join PREFECTURE
     on ENROLLMENT_STATUS.PF_CODE = PREFECTURE.PF_CODE
where SURVEY_YEAR = 2020
  and COLLEGE is not null
group by SURVEY_YEAR, PF_NAME
union all
select SURVEY_YEAR as SV_YEAR
     , ENROLLMENT_STATUS.PF_CODE
     , PF_NAME as PREFECTURE
     , '大学院' as KIND
     , sum(GRADUATE) as AMT
 from ENROLLMENT_STATUS
   inner join PREFECTURE
     on ENROLLMENT_STATUS.PF_CODE = PREFECTURE.PF_CODE
where SURVEY_YEAR = 2020
  and GRADUATE is not null
group by SURVEY_YEAR, PF_NAME
)
order by PF_CODE
       , case kind
         when '小学校' then 1
         when '中学校' then 2
         when '高校' then 3
         when '短大' then 4
         when '大学' then 5
         when '大学院' then 6
         else 9
         end
;