SELECT SURVEY_YEAR AS SV_YEAR,PF_NAME AS PREFECTURE,KIND,AMT
FROM
(
	SELECT SURVEY_YEAR,PF_CODE,KIND,SUM(AMT) AMT,SORT
	FROM (
		SELECT SURVEY_YEAR,PF_CODE,ELEMENTARY AS AMT,'小学校' AS KIND ,0 AS SORT
		FROM
			ENROLLMENT_STATUS
		WHERE ELEMENTARY IS NOT NULL
		UNION
		SELECT SURVEY_YEAR,PF_CODE,MIDDLE AS AMT,'中学校' AS KIND ,1 AS SORT
		FROM
			ENROLLMENT_STATUS
		WHERE MIDDLE IS NOT NULL
		UNION
		SELECT SURVEY_YEAR,PF_CODE,HIGH AS AMT,'高校' AS KIND ,2 AS SORT
		FROM
			ENROLLMENT_STATUS
		WHERE HIGH IS NOT NULL
		UNION
		SELECT SURVEY_YEAR,PF_CODE,JUNIOR_CLG AS AMT,'短大' AS KIND ,3 AS SORT
		FROM
			ENROLLMENT_STATUS
		WHERE JUNIOR_CLG IS NOT NULL
		UNION
		SELECT SURVEY_YEAR,PF_CODE,COLLEGE AS AMT,'大学' AS KIND ,4 AS SORT
		FROM
			ENROLLMENT_STATUS
		WHERE COLLEGE IS NOT NULL
		UNION
		SELECT SURVEY_YEAR,PF_CODE,GRADUATE AS AMT,'大学院' AS KIND ,5 AS SORT
		FROM
			ENROLLMENT_STATUS
		WHERE GRADUATE IS NOT NULL
	)
	WHERE SURVEY_YEAR = '2020'
	GROUP BY SURVEY_YEAR,PF_CODE,KIND
) A
	,PREFECTURE B
WHERE A.PF_CODE = B.PF_CODE
ORDER BY A.PF_CODE,A.SORT