WITH agg AS(
SELECT
	SURVEY_YEAR
	, PF_CODE
	, 1 as so
	, '小学校' as KIND
	, ELEMENTARY as num
FROM
	ENROLLMENT_STATUS

UNION ALL

SELECT
	SURVEY_YEAR
	, PF_CODE
	, 2 as so
	, '中学校' as KIND
	, MIDDLE as num
FROM
	ENROLLMENT_STATUS
	
UNION ALL

SELECT
	SURVEY_YEAR
	, PF_CODE
	, 3 as so
	, '高校' as KIND
	, HIGH as num
FROM
	ENROLLMENT_STATUS
	
UNION ALL

SELECT
	SURVEY_YEAR
	, PF_CODE
	, 4 as so
	, '短大' as KIND
	, JUNIOR_CLG as num
FROM
	ENROLLMENT_STATUS
	
UNION ALL

SELECT
	SURVEY_YEAR
	, PF_CODE
	, 5 as so
	, '大学' as KIND
	, COLLEGE as num
FROM
	ENROLLMENT_STATUS
	
UNION ALL

SELECT
	SURVEY_YEAR
	, PF_CODE
	, 6 as so
	, '大学院' as KIND
	, GRADUATE as num

FROM
	ENROLLMENT_STATUS
)

SELECT
	a.SV_YEAR
	, b.PF_NAME AS PREFECTURE
	, a.KIND
	, a.AMT
FROM
	(
		SELECT
			SURVEY_YEAR as SV_YEAR
			, PF_CODE
			, KIND
			, so
			, SUM(num) AS AMT
		FROM
			agg
		WHERE
			SURVEY_YEAR = '2020'
		GROUP BY
			SV_YEAR
			, PF_CODE
			, KIND	
			, so	
	) as a
INNER JOIN
	PREFECTURE as b
ON
	a.PF_CODE = b.PF_CODE
WHERE
	a.AMT IS NOT NULL
ORDER BY
	a.PF_CODE
	, a.so