select
 SURVEY_YEAR as SV_YEAR
,PF_NAME as PREFECTURE
,KIND
,AMT
from(
select
 SURVEY_YEAR
,PF_CODE
,'小学校' as KIND
,sum(ELEMENTARY) as AMT
,1 as DISP
from
 ENROLLMENT_STATUS
where
 SURVEY_YEAR = 2020
 and ELEMENTARY is not null
group by
 SURVEY_YEAR
,PF_CODE

union all

select
 SURVEY_YEAR
,PF_CODE
,'中学校' as KIND
,sum(MIDDLE) as AMT
,2 as DISP
from
 ENROLLMENT_STATUS
where
 SURVEY_YEAR = 2020
 and MIDDLE is not null
group by
 SURVEY_YEAR
,PF_CODE

union all

select
 SURVEY_YEAR
,PF_CODE
,'高校' as KIND
,sum(HIGH) as AMT
,3 as DISP
from
 ENROLLMENT_STATUS
where
 SURVEY_YEAR = 2020
 and HIGH is not null
group by
 SURVEY_YEAR
,PF_CODE

union all

select
 SURVEY_YEAR
,PF_CODE
,'短大' as KIND
,sum(JUNIOR_CLG) as AMT
,4 as DISP
from
 ENROLLMENT_STATUS
where
 SURVEY_YEAR = 2020
 and JUNIOR_CLG is not null
group by
 SURVEY_YEAR
,PF_CODE

union all

select
 SURVEY_YEAR
,PF_CODE
,'大学' as KIND
,sum(COLLEGE) as AMT
,5 as DISP
from
 ENROLLMENT_STATUS
where
 SURVEY_YEAR = 2020
 and COLLEGE is not null
group by
 SURVEY_YEAR
,PF_CODE

union all

select
 SURVEY_YEAR
,PF_CODE
,'大学院' as KIND
,sum(GRADUATE) as AMT
,6 as DISP
from
 ENROLLMENT_STATUS
where
 SURVEY_YEAR = 2020
 and GRADUATE is not null
group by
 SURVEY_YEAR
,PF_CODE

)main
inner join PREFECTURE
on main.PF_CODE = PREFECTURE.PF_CODE
order by
 main.PF_CODE
,DISP