with base as (
select
    SURVEY_YEAR
    ,PF_CODE
    ,sum(COALESCE(ELEMENTARY,0)) as ELEMENTARY
    ,sum(COALESCE(MIDDLE,0)) as MIDDLE
    ,sum(COALESCE(HIGH,0)) as HIGH
    ,sum(COALESCE(JUNIOR_CLG,0)) as JUNIOR_CLG
    ,sum(COALESCE(COLLEGE,0)) as COLLEGE
    ,sum(COALESCE(GRADUATE,0)) 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!=0
order by
    un.PF_CODE
    ,sorter