select SV_YEAR,PREFECTURE,KIND,AMT
from
(
select SURVEY_YEAR as SV_YEAR
	,PF_NAME as PREFECTURE
	,'小学校' as KIND
	,1 as sortkey
	,sum(ELEMENTARY) as AMT
	,A.PF_CODE

from ENROLLMENT_STATUS A
left outer join PREFECTURE B
on A.PF_CODE = B.PF_CODE 
where SURVEY_YEAR = '2020'

group by SURVEY_YEAR , PF_NAME , A.PF_CODE


-------
union all
select SURVEY_YEAR as SV_YEAR
	,PF_NAME as PREFECTURE
	,'中学校' as KIND
	,2 as sortkey
	,sum(MIDDLE) as AMT
	,A.PF_CODE


from ENROLLMENT_STATUS A
left outer join PREFECTURE B
on A.PF_CODE = B.PF_CODE 
where SURVEY_YEAR = '2020'

group by SURVEY_YEAR , PF_NAME , A.PF_CODE


-------
union all
select SURVEY_YEAR as SV_YEAR
	,PF_NAME as PREFECTURE
	,'高校' as KIND	
	,3 as sortkey
	,sum(HIGH) as AMT	
	,A.PF_CODE

from ENROLLMENT_STATUS A
left outer join PREFECTURE B
on A.PF_CODE = B.PF_CODE 
where SURVEY_YEAR = '2020'

group by SURVEY_YEAR , PF_NAME

-------
union all
select SURVEY_YEAR as SV_YEAR
	,PF_NAME as PREFECTURE

	,'短大' as KIND
	,4 as sortkey
	,sum(JUNIOR_CLG) as AMT
	,A.PF_CODE


from ENROLLMENT_STATUS A
left outer join PREFECTURE B
on A.PF_CODE = B.PF_CODE 
where SURVEY_YEAR = '2020'

group by SURVEY_YEAR , PF_NAME , A.PF_CODE

-------
union all
select SURVEY_YEAR as SV_YEAR
	,PF_NAME as PREFECTURE

	,'大学' as KIND
	,5 as sortkey
	,sum(COLLEGE) as AMT
	,A.PF_CODE


from ENROLLMENT_STATUS A
left outer join PREFECTURE B
on A.PF_CODE = B.PF_CODE 
where SURVEY_YEAR = '2020'

group by SURVEY_YEAR , PF_NAME , A.PF_CODE

-------
union all
select SURVEY_YEAR as SV_YEAR
	,PF_NAME as PREFECTURE

	,'大学院' as KIND
	,6 as sortkey
	,sum(GRADUATE) as AMT
	,A.PF_CODE


from ENROLLMENT_STATUS A
left outer join PREFECTURE B
on A.PF_CODE = B.PF_CODE 
where SURVEY_YEAR = '2020'

group by SURVEY_YEAR , PF_NAME , A.PF_CODE
) subq
where AMT is not null
order by PF_CODE asc,sortkey asc