with tmp_kind as (
    select '小学校' as KIND, 1 as idx
    union all select '中学校' as KIND, 2 as idx
    union all select '高校' as KIND, 3 as idx
    union all select '短大' as KIND, 4 as idx
    union all select '大学' as KIND, 5 as idx
    union all select '大学院' as KIND, 6 as idx
)
select
    t1.SV_YEAR,
    t1.PREFECTURE,
    t2.KIND,
    sum(case t2.KIND
        when '小学校' then t1.ELEMENTARY
        when '中学校' then t1.MIDDLE
        when '高校' then t1.HIGH
        when '短大' then t1.JUNIOR_CLG
        when '大学' then t1.COLLEGE
        when '大学院' then t1.GRADUATE
    end) as AMT
from
    (
        select
            t1.SURVEY_YEAR as SV_YEAR,
            t1.PF_CODE,
            t2.PF_NAME as PREFECTURE,
            sum(t1.ELEMENTARY) as ELEMENTARY,
            sum(t1.MIDDLE) as MIDDLE,
            sum(t1.HIGH) as HIGH,
            sum(t1.JUNIOR_CLG) as JUNIOR_CLG,
            sum(t1.COLLEGE) as COLLEGE,
            sum(t1.GRADUATE) as GRADUATE
        from
            ENROLLMENT_STATUS t1
        inner join
            PREFECTURE t2
            on t1.PF_CODE = t2.PF_CODE
        where
            t1.SURVEY_YEAR = '2020'
        group by 1,2
    ) t1
cross join
    tmp_kind t2
group by 1,2,3
having AMT is not null
order by t1.PF_CODE, t2.idx;