with population as (
    select
        pf_code,
        sum(elementary) as sum1,
        sum(middle) as sum2,
        sum(high) as sum3,
        sum(junior_clg) as sum4,
        sum(college) as sum5,
        sum(graduate) as sum6
    from
        enrollment_status
    where
        survey_year = 2020
    group by
        pf_code
),
kind as (
    select 1 as kind_id, '小学校' as kind_name
    union all select 2, '中学校'
    union all select 3, '高校'
    union all select 4, '短大'
    union all select 5, '大学'
    union all select 6, '大学院'
)
select
    2020 as SV_YEAR,
    PR.pf_name as PREFECTURE,
    K.kind_name as KIND,
    (case K.kind_id
        when 1 then sum1
        when 2 then sum2
        when 3 then sum3
        when 4 then sum4
        when 5 then sum5
        when 6 then sum6
        else 0
    end) as AMT
from
    population P
    cross join kind K
    inner join prefecture PR
    on P.pf_code = PR.pf_code
where
    AMT is not null
order by
    P.pf_code,
    K.kind_id
;