select 
s.sv_year as SV_YEAR,
p.pf_name  as PREFECTURE,
s.kind as KIND,
s.amt as AMT
from 
(
select 
es.survey_year as SV_YEAR
, es.pf_code as pf_code 
, '小学校' as kind
, sum(es.elementary) as amt
, 1 as ord
from 
enrollment_status es 
where 
es.survey_year = 2020
group by 
es.survey_year 
, es.pf_code 
union all 
select 
es.survey_year as SV_YEAR
, es.pf_code as pf_code 
, '中学校' as kind
, sum(es.MIDDLE) as amt
, 2 as ord
from 
enrollment_status es 
where 
es.survey_year = 2020
group by 
es.survey_year 
, es.pf_code 
union all 
select 
es.survey_year as SV_YEAR
, es.pf_code as pf_code 
, '高校' as kind
, sum(es.high) as amt
, 3 as ord
from 
enrollment_status es 
where 
es.survey_year = 2020
group by 
es.survey_year 
, es.pf_code 
union all 
select 
es.survey_year as SV_YEAR
, es.pf_code as pf_code 
, '短大' as kind
, sum(es.junior_clg) as amt
, 4 as ord
from 
enrollment_status es 
where 
es.survey_year = 2020
group by 
es.survey_year 
, es.pf_code 
union all 
select 
es.survey_year as SV_YEAR
, es.pf_code as pf_code 
, '大学' as kind
, sum(es.college) as amt
, 5 as ord
from 
enrollment_status es 
where 
es.survey_year = 2020
group by 
es.survey_year 
, es.pf_code 
union all 
select 
es.survey_year as SV_YEAR
, es.pf_code as pf_code 
, '大学院' as kind
, sum(es.graduate) as amt
, 6 as ord
from 
enrollment_status es 
where 
es.survey_year = 2020
group by 
es.survey_year 
, es.pf_code 
)as s
inner join 
prefecture p 
on
p.pf_code = s.pf_code
where 
s.amt is not null
order by 
p.pf_code asc 
, s.ord asc