with base as (
select
    SURVEY_YEAR
    ,PF_CODE
    ,sum(ELEMENTARY) as ELEMENTARY
    ,sum(MIDDLE) as MIDDLE
    ,sum(HIGH) as HIGH
    ,sum(JUNIOR_CLG) as JUNIOR_CLG
    ,sum(COLLEGE) as COLLEGE
    ,sum(GRADUATE) as GRADUATE
from
    ENROLLMENT_STATUS
where 
    SURVEY_YEAR=2020
group by
    SURVEY_YEAR
    ,PF_CODE
)
,un as (
select SURVEY_YEAR ,PF_CODE ,ELEMENTARY as AMT ,"小学校" as KIND ,1 as sorter from base
union select SURVEY_YEAR ,PF_CODE ,MIDDLE as AMT ,"中学校" as KIND ,2 as sorter from base
union select SURVEY_YEAR ,PF_CODE ,HIGH as AMT ,"高校" as KIND ,3 as sorter from base
union select SURVEY_YEAR ,PF_CODE ,JUNIOR_CLG as AMT ,"短大" as KIND ,4 as sorter from base
union select SURVEY_YEAR ,PF_CODE ,COLLEGE as AMT ,"大学" as KIND ,5 as sorter from base
union select SURVEY_YEAR ,PF_CODE ,GRADUATE as AMT ,"大学院" as KIND ,6 as sorter from base
)

select
    SURVEY_YEAR as SV_YEAR
    ,PF_NAME as PREFECTURE
    ,KIND
    ,AMT
from 
    un
    INNER JOIN
    PREFECTURE as pf
    on un.PF_CODE = pf.PF_CODE
    
where 
    AMT is not null
order by
    un.PF_CODE
    ,sorter