with c as (
    select
        e.SURVEY_YEAR as SV_YEAR,
        e.PF_CODE as PF_CODE,
        p.PF_NAME as PREFECTURE,
        sum(e.ELEMENTARY) as ELEMENTARY,
        sum(e.MIDDLE) as MIDDLE,
        sum(e.HIGH) as HIGH,
        sum(e.JUNIOR_CLG) as JUNIOR_CLG,
        sum(e.COLLEGE) as COLLEGE,
        sum(e.GRADUATE) as GRADUATE
    from
        ENROLLMENT_STATUS e
    inner join
        PREFECTURE p
        on p.PF_CODE = e.PF_CODE
    where e.SURVEY_YEAR = '2020'
    group by e.PF_CODE
)
select
   SV_YEAR,
    PREFECTURE,
    KIND,
    AMT
from (
    select
        SV_YEAR,
        PF_CODE,
        PREFECTURE,
        '小学校' as KIND,
        ELEMENTARY as AMT
    from
        c
    where ELEMENTARY IS NOT NULL
    UNION ALL
    select
        SV_YEAR,
        PF_CODE,
        PREFECTURE,
        '中学校' as KIND,
        MIDDLE as AMT
    from
        c
    where MIDDLE IS NOT NULL
    UNION ALL
    select
        SV_YEAR,
        PF_CODE,
        PREFECTURE,
        '高校' as KIND,
        HIGH as AMT
    from
        c
    where HIGH IS NOT NULL
    UNION ALL
    select
        SV_YEAR,
        PF_CODE,
        PREFECTURE,
        '短大' as KIND,
        JUNIOR_CLG as AMT
    from
        c
    where JUNIOR_CLG IS NOT NULL
    UNION ALL
    select
        SV_YEAR,
        PF_CODE,
        PREFECTURE,
        '大学' as KIND,
        COLLEGE as AMT
    from
        c
    where COLLEGE IS NOT NULL
    UNION ALL
    select
        SV_YEAR,
        PF_CODE,
        PREFECTURE,
        '大学院' as KIND,
        GRADUATE as AMT
    from
        c
    where GRADUATE IS NOT NULL
)
order by PF_CODE;