select
    2020 as SV_YEAR,
    prefecture.pf_name as PREFECTURE,
    (case population.kind_id
        when 1 then '小学校'
        when 2 then '中学校'
        when 3 then '高校'
        when 4 then '短大'
        when 5 then '大学'
        when 6 then '大学院'
        else ''
    end) as KIND,
    population.amt as AMT
from (
        select pf_code, sum(elementary) as amt, 1 as kind_id from enrollment_status where survey_year = 2020 group by pf_code
        union all
        select pf_code, sum(middle) as amt, 2 as kind_id from enrollment_status where survey_year = 2020 group by pf_code
        union all
        select pf_code, sum(high) as amt, 3 as kind_id from enrollment_status where survey_year = 2020 group by pf_code
        union all
        select pf_code, sum(junior_clg) as amt, 4 as kind_id from enrollment_status where survey_year = 2020 group by pf_code
        union all
        select pf_code, sum(college) as amt, 5 as kind_id from enrollment_status where survey_year = 2020 group by pf_code
        union all
        select pf_code, sum(graduate) as amt, 6 as kind_id from enrollment_status where survey_year = 2020 group by pf_code
    ) as population
    inner join prefecture
    using (pf_code)
where
    amt is not null
order by
    pf_code,
    kind_id
;