select
result.SURVEY_YEAR as SV_YEAR
,pre.PF_NAME as PREFECTURE
,result.KIND as KIND
,result.AMT as AMT
from
(
select
SURVEY_YEAR
,PF_CODE
,'小学校' as KIND
,SUM(ELEMENTARY) as AMT
from
ENROLLMENT_STATUS
where
SURVEY_YEAR = 2020
GROUP BY(PF_CODE)
union all
--中学校
select
SURVEY_YEAR
,PF_CODE
,'中学校' as KIND
,SUM(MIDDLE) as AMT
from
ENROLLMENT_STATUS
where
SURVEY_YEAR = 2020
GROUP BY(PF_CODE)
--高校
union all
select
SURVEY_YEAR
,PF_CODE
,'高校' as KIND
,SUM(HIGH) as AMT
from
ENROLLMENT_STATUS
where
SURVEY_YEAR = 2020
GROUP BY(PF_CODE)
--短大
union all
select
SURVEY_YEAR
,PF_CODE
,'短大' as KIND
,SUM(JUNIOR_CLG) as AMT
from
ENROLLMENT_STATUS
where
SURVEY_YEAR = 2020
GROUP BY(PF_CODE)
--大学
union all
select
SURVEY_YEAR
,PF_CODE
,'大学' as KIND
,SUM(COLLEGE) as AMT
from
ENROLLMENT_STATUS
where
SURVEY_YEAR = 2020
GROUP BY(PF_CODE)
--大学院
union all
select
SURVEY_YEAR
,PF_CODE
,'大学院' as KIND
,SUM(GRADUATE) as AMT
from
ENROLLMENT_STATUS
where
SURVEY_YEAR = 2020
GROUP BY(PF_CODE)
) result
inner join
PREFECTURE pre
on
result.PF_CODE = pre.PF_CODE
where
result.AMT is not null
order by
result.PF_CODE