with base as (
select SURVEY_YEAR,
       PF_CODE,
       "小学校" as KIND,
       total(ELEMENTARY) AMT,
       0 as ord
from ENROLLMENT_STATUS
where SURVEY_YEAR = 2020
group by SURVEY_YEAR,
       PF_CODE
UNION all 
select SURVEY_YEAR,
       PF_CODE,
       "中学校" as KIND,
       total(MIDDLE)  AMT,
       1 as ord
from ENROLLMENT_STATUS
where SURVEY_YEAR = 2020
group by SURVEY_YEAR,
       PF_CODE
UNION all 
select SURVEY_YEAR,
       PF_CODE,
       "高校" as KIND,
       total(HIGH)  AMT,
       2 as ord
from ENROLLMENT_STATUS
where SURVEY_YEAR = 2020
group by SURVEY_YEAR,
       PF_CODE
UNION all 
select SURVEY_YEAR,
       PF_CODE,
       "短大" as KIND,
       total(JUNIOR_CLG) AMT,
       3 as ord
from ENROLLMENT_STATUS
where SURVEY_YEAR = 2020
group by SURVEY_YEAR,
       PF_CODE
UNION all 
select SURVEY_YEAR,
       PF_CODE,
       "大学" as KIND,
       total(COLLEGE) AMT,
       4 as ord
from ENROLLMENT_STATUS
where SURVEY_YEAR = 2020
group by SURVEY_YEAR,
       PF_CODE
UNION all 
select SURVEY_YEAR,
       PF_CODE,
       "大学院" as KIND,
       total(GRADUATE) AMT,
       5 as ord
from ENROLLMENT_STATUS
where SURVEY_YEAR = 2020
group by SURVEY_YEAR,
       PF_CODE

)
select 
SURVEY_YEAR as SV_YEAR,
PF_NAME as PREFECTURE,
KIND,
AMT
 from base b
 inner join PREFECTURE p on p.pf_code = b.pf_code
 where AMT > 0
order by b.PF_CODE, b.ord