with base as (
select SURVEY_YEAR,PF_CODE,1 as sk,"小学校" as name,  ELEMENTARY as AMT from ENROLLMENT_STATUS where SURVEY_YEAR = 2020 and ELEMENTARY is not null union all
select SURVEY_YEAR,PF_CODE,2 as sk,"中学校" as name,  MIDDLE as AMT from ENROLLMENT_STATUS where SURVEY_YEAR = 2020 and MIDDLE is not null union all
select SURVEY_YEAR,PF_CODE,3 as sk,"高校" as name,  HIGH as AMT from ENROLLMENT_STATUS where SURVEY_YEAR = 2020 and HIGH is not null union all
select SURVEY_YEAR,PF_CODE,4 as sk,"短大" as name,  JUNIOR_CLG as AMT from ENROLLMENT_STATUS where SURVEY_YEAR = 2020 and JUNIOR_CLG is not null union all
select SURVEY_YEAR,PF_CODE,5 as sk,"大学" as name,  COLLEGE as AMT from ENROLLMENT_STATUS where SURVEY_YEAR = 2020 and COLLEGE is not null union all
select SURVEY_YEAR,PF_CODE,6 as sk,"大学院" as name,  GRADUATE as AMT from ENROLLMENT_STATUS where SURVEY_YEAR = 2020 and GRADUATE is not null
)
select
a.SURVEY_YEAR as 'SV_YEAR',
b.PF_NAME as 'PREFECTURE',
a.name as 'KIND',
sum(a.AMT) as 'AMT'
from base a
inner join PREFECTURE b
on a.PF_CODE=b.PF_CODE
group by SURVEY_YEAR, PF_NAME, name
order by a.PF_CODE,a.sk