select
SURVEY_YEAR as SV_YEAR
,PREFECTURE.PF_NAME as PREFECTURE
,name as KIND
,test as AMT
from
(
select
SURVEY_YEAR
,PF_CODE
,1 as rank
,'小学校' as name
,sum(cast(ELEMENTARY as REAL)) as test
from
ENROLLMENT_STATUS
where SURVEY_YEAR = 2020
group by PF_CODE
UNION 
select
SURVEY_YEAR
,PF_CODE
,2 as rank
,'中学校' as name
,sum(cast(MIDDLE as REAL)) as test
from
ENROLLMENT_STATUS
where SURVEY_YEAR = 2020
group by PF_CODE
UNION 
select
SURVEY_YEAR
,PF_CODE
,3 as rank
,'高校' as name
,sum(cast(HIGH as REAL)) as test
from
ENROLLMENT_STATUS
where SURVEY_YEAR = 2020
group by PF_CODE
UNION 
select
SURVEY_YEAR
,PF_CODE
,4 as rank
,'短大' as name
,sum(cast(JUNIOR_CLG as REAL)) as test
from
ENROLLMENT_STATUS
where SURVEY_YEAR = 2020
group by PF_CODE
UNION 
select
SURVEY_YEAR
,PF_CODE
,5 as rank
,'大学' as name
,sum(cast(COLLEGE as REAL)) as test
from
ENROLLMENT_STATUS
where SURVEY_YEAR = 2020
group by PF_CODE
UNION 
select
SURVEY_YEAR
,PF_CODE
,6 as rank
,'大学科' as name
,sum(cast(GRADUATE as REAL)) as test
from
ENROLLMENT_STATUS
where SURVEY_YEAR = 2020
group by PF_CODE
) aaa inner join PREFECTURE on 
aaa.PF_CODE = PREFECTURE.PF_CODE
where test is not null
order by aaa.PF_CODE,rank;