WITH Pre AS(
SELECT
    	E.SURVEY_YEAR AS SV_YEAR,
    	E.PF_CODE,
	P.PF_NAME AS PREFECTURE,
    	SUM(ELEMENTARY) AS ELEMENTARY,
    	SUM(MIDDLE) AS MIDDLE,
    	SUM(HIGH) AS HIGH,
    	SUM(JUNIOR_CLG) AS JUNIOR_CLG,
    	SUM(COLLEGE) AS COLLEGE,
	SUM(GRADUATE) AS GRADUATE
FROM
	ENROLLMENT_STATUS AS E
INNER JOIN
	PREFECTURE AS P
ON
	E.PF_CODE = P.PF_CODE
WHERE
    SURVEY_YEAR = 2020
GROUP BY
    E.PF_CODE
),
Pre2 AS (
SELECT
	PF_CODE,
	SV_YEAR,
	PREFECTURE,
	'小学校' AS KIND,
	ELEMENTARY AS AMT
FROM
	Pre
WHERE
	AMT IS NOT NULL
UNION ALL
SELECT
	PF_CODE,
	SV_YEAR,
	PREFECTURE,
	'中学校' AS KIND,
	MIDDLE AS AMT
FROM
	Pre
WHERE
	AMT IS NOT NULL
UNION ALL
SELECT
	PF_CODE,
	SV_YEAR,
	PREFECTURE,
	'高校' AS KIND,
	HIGH AS AMT
FROM
	Pre
WHERE
	AMT IS NOT NULL
UNION ALL
SELECT
	PF_CODE,
	SV_YEAR,
	PREFECTURE,
	'短大' AS KIND,
	JUNIOR_CLG AS AMT
FROM
	Pre
WHERE
	AMT IS NOT NULL
UNION ALL
SELECT
	PF_CODE,
	SV_YEAR,
	PREFECTURE,
	'大学' AS KIND,
	COLLEGE AS AMT
FROM
	Pre
WHERE
	AMT IS NOT NULL
UNION ALL
SELECT
	PF_CODE,
	SV_YEAR,
	PREFECTURE,
	'大学院' AS KIND,
	GRADUATE AS AMT
FROM
	Pre
WHERE
	AMT IS NOT NULL
)
SELECT
	SV_YEAR,
	PREFECTURE,
	KIND,
	AMT
FROM
	Pre2
ORDER BY
	PF_CODE