with s as ( 
select pf_code, sum(ELEMENTARY) as e, sum(middle) as m, sum(high) as h, sum(junior_clg) as j, sum(college) as c, sum(graduate) as g
from enrollment_status where survey_year = 2020 group by pf_code
)
, e as (
select pf_code, '小学校' as KIND, 0 as KIND_CODE, e as AMT
from s
where e is not null
)
, m as (
select pf_code, '中学校' as KIND, 1 as KIND_CODE, m as AMT
from s
where m is not null
)
, h as (
select pf_code, '高校' as KIND, 2 as KIND_CODE, h as AMT
from s
where h is not null
)
, j as (
select pf_code, '短大' as KIND, 3 as KIND_CODE, j as AMT
from s
where j is not null
)
, c as (
select pf_code, '大学' as KIND, 4 as KIND_CODE, c as AMT
from s
where c is not null
)
, g as (
select pf_code, '大学院' as KIND, 5 as KIND_CODE, g as AMT
from s
where g is not null
)
, agg as (select * from e union all 
select * from m union all 
select * from h union all
select * from j union all
select * from c union all
select * from g
)
select 2020 as SV_YEAR, prefecture.pf_name as PREFECTURE, agg.KIND as KIND, agg.AMT as AMT from agg join prefecture on agg.pf_code = prefecture.pf_code
order by agg.pf_code, agg.KIND_CODE