with
a as (
    select SURVEY_YEAR,PF_CODE,'小学校' as KIND, 1 as rn,sum(ELEMENTARY) as AMT from ENROLLMENT_STATUS group by SURVEY_YEAR,PF_CODE
    union all
    select SURVEY_YEAR,PF_CODE,'中学校' as KIND, 2 as rn,sum(MIDDLE) as AMT from ENROLLMENT_STATUS group by SURVEY_YEAR,PF_CODE
    union all
    select SURVEY_YEAR,PF_CODE,'高校' as KIND, 3 as rn,sum(HIGH) as AMT from ENROLLMENT_STATUS group by SURVEY_YEAR,PF_CODE
    union all
    select SURVEY_YEAR,PF_CODE,'短大' as KIND, 4 as rn,sum(JUNIOR_CLG) as AMT from ENROLLMENT_STATUS group by SURVEY_YEAR,PF_CODE
    union all
    select SURVEY_YEAR,PF_CODE,'大学' as KIND, 5 as rn,sum(COLLEGE) as AMT from ENROLLMENT_STATUS group by SURVEY_YEAR,PF_CODE
    union all
    select SURVEY_YEAR,PF_CODE,'大学院' as KIND, 6 as rn,sum(GRADUATE) as AMT from ENROLLMENT_STATUS group by SURVEY_YEAR,PF_CODE
)
select 
    a.SURVEY_YEAR as SV_YEAR
    ,p.PF_NAME as PREFECTURE
    ,a.KIND 
    ,a.AMT
from a
join PREFECTURE p
    on a.PF_CODE = p.PF_CODE
where SV_YEAR = 2020
and a.AMT IS NOT NULL
order by p.PF_CODE, a.rn