with level as(
    select 1 level union all
    select 2 union all
    select 3 union all
    select 4 union all
    select 5 union all
    select 6
)


select
    temp.survey_year SV_YEAR,
    temp.pf_name PREFECTURE,
    temp.kind KIND,
    sum(temp.amt) AMT
from
(
    select
        e.survey_year,
        p.pf_name,
        p.pf_code,
        l.level,
        case 
            when l.level = 1 then '小学校'
            when l.level = 2 then '中学校'
            when l.level = 3 then '高校'
            when l.level = 4 then '短大'
            when l.level = 5 then '大学'
            when l.level = 6 then '大学院'
            else null 
        end kind,
        case 
            when l.level = 1 then e.elementary
            when l.level = 2 then e.middle
            when l.level = 3 then e.high
            when l.level = 4 then e.junior_clg
            when l.level = 5 then e.college
            when l.level = 6 then e.graduate
            else null 
        end amt
    from enrollment_status e
    join prefecture p on e.pf_code = p.pf_code
    join level l
    where 1 = 1
    and e.survey_year = 2020
)temp
where temp.amt is not null
group by temp.survey_year, temp.pf_name, temp.kind
order by temp.pf_code asc, temp.level 
;