select SV_YEAR
, PREFECTURE
, KIND
, AMT
from
(
select
SURVEY_YEAR as SV_YEAR
,PF_NAME as PREFECTURE
,'小学校' as kind
, 1 as class
,sum(case when ELEMENTARY is null then 0 else ELEMENTARY end) as AMT
from ENROLLMENT_STATUS a
inner join PREFECTURE b
on a.PF_CODE = b.PF_CODE
where SURVEY_YEAR = '2020'
group by SURVEY_YEAR, b.PF_CODE
union all
select
SURVEY_YEAR as SV_YEAR
,PF_NAME as PREFECTURE
,'中学校' as kind
, 2 as class
,sum(case when MIDDLE is null then 0 else MIDDLE end) as AMT
from ENROLLMENT_STATUS a
inner join PREFECTURE b
on a.PF_CODE = b.PF_CODE
where SURVEY_YEAR = '2020'
group by SURVEY_YEAR, b.PF_CODE
union all
select
SURVEY_YEAR as SV_YEAR
,PF_NAME as PREFECTURE
,'高校' as kind
, 3 as class
,sum(case when HIGH is null then 0 else HIGH end) as AMT
from ENROLLMENT_STATUS a
inner join PREFECTURE b
on a.PF_CODE = b.PF_CODE
where SURVEY_YEAR = '2020'
group by SURVEY_YEAR, b.PF_CODE
union all
select
SURVEY_YEAR as SV_YEAR
,PF_NAME as PREFECTURE
,'短大' as kind
, 4 as class
,sum(case when JUNIOR_CLG is null then 0 else JUNIOR_CLG end) as AMT
from ENROLLMENT_STATUS a
inner join PREFECTURE b
on a.PF_CODE = b.PF_CODE
where SURVEY_YEAR = '2020'
group by SURVEY_YEAR, b.PF_CODE
union all
select
SURVEY_YEAR as SV_YEAR
,PF_NAME as PREFECTURE
,'大学' as kind
, 4 as class
,sum(case when COLLEGE is null then 0 else COLLEGE end) as AMT
from ENROLLMENT_STATUS a
inner join PREFECTURE b
on a.PF_CODE = b.PF_CODE
where SURVEY_YEAR = '2020'
group by SURVEY_YEAR, b.PF_CODE
union all
select
SURVEY_YEAR as SV_YEAR
,PF_NAME as PREFECTURE
,'大学院' as kind
, 5 as class
,sum(case when GRADUATE is null then 0 else GRADUATE end) as AMT
from ENROLLMENT_STATUS a
inner join PREFECTURE b
on a.PF_CODE = b.PF_CODE
where SURVEY_YEAR = '2020'
group by SURVEY_YEAR, b.PF_CODE
) as table_a
order by PREFECTURE, class