with t as (
    select e.PF_CODE, p.PF_NAME,
    sum(ELEMENTARY) as s0,
    sum(MIDDLE) as s1,
    sum(HIGH) as s2,
    sum(JUNIOR_CLG) as s3,
    sum(COLLEGE) as s4,
    sum(GRADUATE) as s5
    from ENROLLMENT_STATUS as e
    inner join PREFECTURE as p on p.PF_CODE=e.PF_CODE
    where e.survey_year=2020
    group by e.PF_CODE, p.PF_NAME
), u as (
    select 2020 as "SV_YEAR",
    pf_name as "PREFECTURE",
    "小学校" as "KIND",
    s0 as "AMT", PF_CODE, 0 as k
    from t
    union all
    select 2020 as "SV_YEAR",
    pf_name as "PREFECTURE",
    "中学校" as "KIND",
    s1 as "AMT", PF_CODE, 1 as k
    from t
    union all
    select 2020 as "SV_YEAR",
    pf_name as "PREFECTURE",
    "高校" as "KIND",
    s2 as "AMT", PF_CODE, 2 as k
    from t
    union all
    select 2020 as "SV_YEAR",
    pf_name as "PREFECTURE",
    "短大" as "KIND",
    s3 as "AMT", PF_CODE, 3 as k
    from t
    union all
    select 2020 as "SV_YEAR",
    pf_name as "PREFECTURE",
    "大学" as "KIND",
    s4 as "AMT", PF_CODE, 4 as k
    from t
    union all
    select 2020 as "SV_YEAR",
    pf_name as "PREFECTURE",
    "大学院" as "KIND",
    s5 as "AMT", PF_CODE, 5 as k
    from t
)
select SV_YEAR,PREFECTURE,KIND,AMT
from u
where AMT!=0
order by pf_code, k