select
	k.SURVEY_YEAR as SV_YEAR
	,p.PF_NAME as PREFECTURE
	,k.col as KIND
	,k.val as AMT
from
	(
		select
			1 as num
			,SURVEY_YEAR
			,PF_CODE
			,sum(ELEMENTARY) as val
			,'小学校' as col
		from
			ENROLLMENT_STATUS
		group by
			SURVEY_YEAR, PF_CODE
		union all
		select
			2 as num
			,SURVEY_YEAR
			,PF_CODE
			,sum(MIDDLE) as val
			,'中学校' as col
		from
			ENROLLMENT_STATUS
		group by
			SURVEY_YEAR, PF_CODE
		union all
		select
			3 as num
			,SURVEY_YEAR
			,PF_CODE
			,sum(HIGH) as val
			,'高校' as col
		from
			ENROLLMENT_STATUS
		group by
			SURVEY_YEAR, PF_CODE
		union all
		select
			4 as num
			,SURVEY_YEAR
			,PF_CODE
			,sum(JUNIOR_CLG) as val
			,'短大' as col
		from
			ENROLLMENT_STATUS
		group by
			SURVEY_YEAR, PF_CODE
		union all
		select
			5 as num
			,SURVEY_YEAR
			,PF_CODE
			,sum(COLLEGE) as val
			,'大学' as col
		from
			ENROLLMENT_STATUS
		group by
			SURVEY_YEAR, PF_CODE
		union all
		select
			6 as num
			,SURVEY_YEAR
			,PF_CODE
			,sum(GRADUATE) as val
			,'大学院' as col
		from
			ENROLLMENT_STATUS
		group by
			SURVEY_YEAR, PF_CODE
	) as k
	left join PREFECTURE as p
		on k.PF_CODE = p.PF_CODE
where
	k.SURVEY_YEAR = 2020
and k.val is not null
order by
	k.PF_CODE
	,k.num