with tbl as (
select
es.SURVEY_YEAR as [SV_YEAR],
es.PF_CODE as [PF_CODE],
sum(ELEMENTARY) as [小学校],
sum(MIDDLE) as [中学校],
sum(HIGH) as [高卒],
sum(JUNIOR_CLG) as [短大],
sum(COLLEGE) as [大卒],
sum(GRADUATE) as [大学院]
--集計した就学先の種類 → KIND
--就学先ごとに集計した人数 → AMT
from
ENROLLMENT_STATUS as es
where es.SURVEY_YEAR = '2020'
group by 
es.SURVEY_YEAR,
es.PF_CODE
)
select
t.sv_year as [SV_YEAR], p.pf_name as [PREFECTURE], [KIND], [AMT]
from (
select
sv_year, pf_code, 1 as [k_order], '小学校' as [KIND], 小学校 as [AMT]
from tbl
union all
select
sv_year, pf_code, 2 as [k_order], '中学校' as [KIND], 中学校 as [AMT]
from tbl
union all
select
sv_year, pf_code, 3 as [k_order], '高校' as [KIND], 高卒 as [AMT]
from tbl
union all
select
sv_year, pf_code, 4 as [k_order], '短大' as [KIND], 短大 as [AMT]
from tbl
union all
select
sv_year, pf_code, 5 as [k_order], '大学' as [KIND], 大卒 as [AMT]
from tbl
union all
select
sv_year, pf_code, 6 as [k_order], '大学院' as [KIND], 大学院 as [AMT]
from tbl
) as t
left outer join PREFECTURE as p
 on t.pf_code = p.pf_code
 where AMT is not null
order by p.pf_code, t.k_order