with sho as(
select PF_CODE, sum (ELEMENTARY) as AMT, "小学校" as KIND, 1 as num from ENROLLMENT_STATUS
where SURVEY_YEAR = 2020
group by PF_CODE
), chu as(
select PF_CODE, sum (MIDDLE) as AMT, "中学校" as KIND,2 as num  from ENROLLMENT_STATUS
where SURVEY_YEAR = 2020
group by PF_CODE
), kou as (
select PF_CODE, sum (HIGH) as AMT, "高校" as KIND,3 as num  from ENROLLMENT_STATUS
where SURVEY_YEAR = 2020
group by PF_CODE
), jun as (
select PF_CODE, sum (JUNIOR_CLG) as AMT, "短大" as KIND,4 as num  from ENROLLMENT_STATUS
where SURVEY_YEAR = 2020
group by PF_CODE
), col as(
select PF_CODE, sum (COLLEGE) as AMT, "大学" as KIND,5 as num  from ENROLLMENT_STATUS
where SURVEY_YEAR = 2020
group by PF_CODE
), gra as(
select PF_CODE, sum (GRADUATE) as AMT, "大学院" as KIND,6 as num  from ENROLLMENT_STATUS
where SURVEY_YEAR = 2020
group by PF_CODE
), tmp as(
select * from sho
union all
select * from chu
union all
select * from kou
union all
select * from jun
union all
select * from col
union all
select * from gra
)

select 2020 as SV_YEAR , p.PF_NAME as PREFECTURE, t.KIND, t.AMT from PREFECTURE p
left join tmp t on p.PF_CODE = t.PF_CODE
where t.AMT is not null
order by p.PF_CODE, t.num