with BASE as(
select
	A.SURVEY_YEAR as SV_YEAR
,	A.PF_CODE
,	B.PF_NAME as PREFECTURE
,	SUM(A.ELEMENTARY) as ELEMENTARY
,	SUM(A.MIDDLE) as MIDDLE
,	SUM(A.HIGH) as HIGH
,	SUM(A.JUNIOR_CLG) as JUNIOR_CLG
,	SUM(A.COLLEGE) as COLLEGE
,	SUM(A.GRADUATE) as GRADUATE
from
	ENROLLMENT_STATUS A
,	PREFECTURE B
where
	A.PF_CODE = B.PF_CODE
and	A.SURVEY_YEAR = '2020'
group by
	SV_YEAR
,	A.PF_CODE
,	PREFECTURE
),
BASE2 as(
select
	SV_YEAR
,	PF_CODE
,	PREFECTURE
,	'小学校' AS KIND
,	'1' as NUM
,	ELEMENTARY as AMT
from
	BASE
where
	ELEMENTARY IS NOT NULL
union
select
	SV_YEAR
,	PF_CODE
,	PREFECTURE
,	'中学校' AS KIND
,	'2' as NUM
,	MIDDLE as AMT
from
	BASE
where
	MIDDLE IS NOT NULL
union
select
	SV_YEAR
,	PF_CODE
,	PREFECTURE
,	'高校' AS KIND
,	'3' as NUM
,	HIGH as AMT
from
	BASE
where
	HIGH IS NOT NULL
union
select
	SV_YEAR
,	PF_CODE
,	PREFECTURE
,	'短大' AS KIND
,	'4' as NUM
,	JUNIOR_CLG as AMT
from
	BASE
where
	JUNIOR_CLG IS NOT NULL
union
select
	SV_YEAR
,	PF_CODE
,	PREFECTURE
,	'大学' AS KIND
,	'5' as NUM
,	COLLEGE as AMT
from
	BASE
where
	COLLEGE IS NOT NULL
union
select
	SV_YEAR
,	PF_CODE
,	PREFECTURE
,	'大学院' AS KIND
,	'6' as NUM
,	GRADUATE as AMT
from
	BASE
where
	GRADUATE IS NOT NULL
)
SELECT
	SV_YEAR
,	PREFECTURE
,	KIND
,	AMT
from
	BASE2
order by
	PF_CODE
,	NUM