with yoko as (
select survey_year as "SV_YEAR",
       e.pf_code,
       pf_name as "PREFECTURE",
       elementary,
       middle,
       high,
       junior_clg,
       college,
       graduate
from ENROLLMENT_STATUS as e
inner join prefecture as p on p.pf_code = e.pf_code
where survey_year = '2020'
),

p as (
select '小学校' as "KIND", 0 as "num"
union all
select '中学校' as "KIND", 1 as "num"
union all
select '高校' as "KIND", 2 as "num"
union all
select '短大' as "KIND", 3 as "num"
union all
select '大学' as "KIND", 4 as "num"
union all
select '大学院' as "KIND", 5 as "num"
),

tra as (
select sv_year as "SV_YEAR",
       prefecture as "PREFECTURE",
       pf_code,
       num,
       kind as "KIND",
       case kind when '小学校' then elementary
                 when '中学校' then middle
                 when '高校' then high
                 when '短大' then junior_clg
                 when '大学' then college
                 when '大学院' then graduate end as "AMT"
from yoko
cross join p 
order by pf_code,num 
)

select sv_year as "SV_YEAR",
       prefecture as "PREFECTURE",
       kind as "KIND",
       sum(AMT) as AMT
from tra
where AMT is not null
group by 1,2,3
order by pf_code, num