select SURVEY_YEAR as SV_YEAR
, PF_NAME as PREFECTURE
, KIND
, AMT
from (
select e.SURVEY_YEAR, p.PF_NAME, p.PF_CODE
,1 as sk, "小学校" as KIND, sum(e.ELEMENTARY) as AMT
from ENROLLMENT_STATUS e
     inner join PREFECTURE p
       on p.PF_CODE = e.PF_CODE
where e.SURVEY_YEAR = 2020 and e.ELEMENTARY is not null
group by e.SURVEY_YEAR, p.PF_NAME, p.PF_CODE

union all
select e.SURVEY_YEAR, p.PF_NAME, p.PF_CODE
,2 as sk, "中学校" as KIND, sum(e.MIDDLE) as AMT
from ENROLLMENT_STATUS e
     inner join PREFECTURE p
       on p.PF_CODE = e.PF_CODE
where e.SURVEY_YEAR = 2020 and e.MIDDLE is not null
group by e.SURVEY_YEAR, p.PF_NAME, p.PF_CODE

union all
select e.SURVEY_YEAR, p.PF_NAME, p.PF_CODE
,3 as sk, "高校" as KIND, sum(e.HIGH) as AMT
from ENROLLMENT_STATUS e
     inner join PREFECTURE p
       on p.PF_CODE = e.PF_CODE
where e.SURVEY_YEAR = 2020 and e.HIGH is not null
group by e.SURVEY_YEAR, p.PF_NAME, p.PF_CODE

union all
select e.SURVEY_YEAR, p.PF_NAME, p.PF_CODE
,4 as sk, "短大" as KIND, sum(e.JUNIOR_CLG) as AMT
from ENROLLMENT_STATUS e
     inner join PREFECTURE p
       on p.PF_CODE = e.PF_CODE
where e.SURVEY_YEAR = 2020 and e.JUNIOR_CLG is not null
group by e.SURVEY_YEAR, p.PF_NAME, p.PF_CODE

union all
select e.SURVEY_YEAR, p.PF_NAME, p.PF_CODE
,5 as sk, "大学" as KIND, sum(e.COLLEGE) as AMT
from ENROLLMENT_STATUS e
     inner join PREFECTURE p
       on p.PF_CODE = e.PF_CODE
where e.SURVEY_YEAR = 2020 and e.COLLEGE is not null
group by e.SURVEY_YEAR, p.PF_NAME, p.PF_CODE

union all
select e.SURVEY_YEAR, p.PF_NAME, p.PF_CODE
,6 as sk, "大学院" as KIND, sum(e.GRADUATE) as AMT
from ENROLLMENT_STATUS e
     inner join PREFECTURE p
       on p.PF_CODE = e.PF_CODE
where e.SURVEY_YEAR = 2020 and e.GRADUATE is not null
group by e.SURVEY_YEAR, p.PF_NAME, p.PF_CODE
)
order by PF_CODE
, sk