with enrollments as (
    select
        pf_code,
        sum(elementary) as kind1,
        sum(middle) as kind2,
        sum(high) as kind3,
        sum(junior_clg) as kind4,
        sum(college) as kind5,
        sum(graduate) as kind6
    from
        enrollment_status
    where
        survey_year = 2020
    group by
        pf_code
),
kinds as (
    select 'kind1' as kind
    union select 'kind2'
    union select 'kind3'
    union select 'kind4'
    union select 'kind5'
    union select 'kind6'
)
select
    2020 as SV_YEAR,
    P.pf_name as PREFECTURE,
    (case K.kind
        when 'kind1' then '小学校'
        when 'kind2' then '中学校'
        when 'kind3' then '高校'
        when 'kind4' then '短大'
        when 'kind5' then '大学'
        when 'kind6' then '大学院'
        else ''
    end) as KIND,
    (case K.kind
        when 'kind1' then E.kind1
        when 'kind2' then E.kind2
        when 'kind3' then E.kind3
        when 'kind4' then E.kind4
        when 'kind5' then E.kind5
        when 'kind6' then E.kind6
        else 0
    end) as AMT
from
    enrollments as E
    cross join kinds as K
    inner join prefecture P
    on E.pf_code = P.pf_code
where
    amt is not null
order by
    E.pf_code,
    K.kind
;