with base_table as (
select
  pf_code as code,
  '小学校' as kind,
  1 as order_rank,
  sum(elementary) as total
from enrollment_status
where survey_year = 2020 and elementary is not null
group by 1,2,3
union
select
  pf_code as code,
  '中学校' as kind,
  2 as order_rank,
  sum(middle) as total
from enrollment_status
where survey_year = 2020 and middle is not null
group by 1,2,3
union
select
  pf_code as code,
  '高校' as kind,
  3 as order_rank,
  sum(high) as total
from enrollment_status
where survey_year = 2020 and high is not null
group by 1,2,3
union
select
  pf_code as code,
  '短大' as kind,
  4 as order_rank,
  sum(junior_clg) as total
from enrollment_status
where survey_year = 2020 and junior_clg is not null
group by 1,2,3
union
select
  pf_code as code,
  '大学' as kind,
  5 as order_rank,
  sum(college) as total
from enrollment_status
where survey_year = 2020 and college is not null
group by 1,2,3
union
select
  pf_code as code,
  '大学院' as kind,
  6 as order_rank,
  sum(graduate) as total
from enrollment_status
where survey_year = 2020 and graduate is not null
group by 1,2,3
)
select
  2020 as SV_YEAR,
  t1.pf_name as PREFECTURE,
  t2.kind as KIND,
  t2.total as AMT
from PREFECTURE t1
inner join base_table t2
  on t1.pf_code = t2.code
order by t1.pf_code asc, t2.order_rank asc
;