SELECT 
	SURVEY_YEAR AS SV_YEAR,
	PF_NAME AS PREFECTURE,
	KIND,
	AMT
FROM
(
SELECT
	SURVEY_YEAR,
	P.PF_CODE,
	P.PF_NAME,
	'小学校' AS KIND,
	0 AS OKEY,
	SUM(ELEMENTARY) AS AMT
FROM
	ENROLLMENT_STATUS S
INNER JOIN
    PREFECTURE P
ON
    S.PF_CODE = P.PF_CODE
WHERE
	S.SURVEY_YEAR = 2020
GROUP BY
    S.SURVEY_YEAR, P.PF_NAME
HAVING
    AMT IS NOT NULL
UNION ALL
SELECT
	SURVEY_YEAR,
	P.PF_CODE,
	P.PF_NAME,
	'中学校' AS KIND,
	1 AS OKEY,
	SUM(MIDDLE) AS AMT
FROM
	ENROLLMENT_STATUS S
INNER JOIN
    PREFECTURE P
ON
    S.PF_CODE = P.PF_CODE
WHERE
	S.SURVEY_YEAR = 2020
GROUP BY
    S.SURVEY_YEAR, P.PF_CODE, P.PF_NAME
HAVING
    AMT IS NOT NULL
UNION ALL
SELECT
	SURVEY_YEAR,
	P.PF_CODE,
	P.PF_NAME,
	'高校' AS KIND,
	2 AS OKEY,
	SUM(HIGH) AS AMT
FROM
	ENROLLMENT_STATUS S
INNER JOIN
    PREFECTURE P
ON
    S.PF_CODE = P.PF_CODE
WHERE
	S.SURVEY_YEAR = 2020
GROUP BY
    S.SURVEY_YEAR, P.PF_CODE, P.PF_NAME
HAVING
    AMT IS NOT NULL
UNION ALL
SELECT
	SURVEY_YEAR,
	P.PF_CODE,
	P.PF_NAME,
	'短大' AS KIND,
	3 AS OKEY,
	SUM(JUNIOR_CLG) AS AMT
FROM
	ENROLLMENT_STATUS S
INNER JOIN
    PREFECTURE P
ON
    S.PF_CODE = P.PF_CODE
WHERE
	S.SURVEY_YEAR = 2020
GROUP BY
    S.SURVEY_YEAR, P.PF_CODE, P.PF_NAME
HAVING
    AMT IS NOT NULL
UNION ALL
SELECT
	SURVEY_YEAR,
	P.PF_CODE,
	P.PF_NAME,
	'大学' AS KIND,
	4 AS OKEY,
	SUM(COLLEGE) AS AMT
FROM
	ENROLLMENT_STATUS S
INNER JOIN
    PREFECTURE P
ON
    S.PF_CODE = P.PF_CODE
WHERE
	S.SURVEY_YEAR = 2020
GROUP BY
    S.SURVEY_YEAR, P.PF_CODE, P.PF_NAME
HAVING
    AMT IS NOT NULL
UNION ALL
SELECT
	SURVEY_YEAR,
	P.PF_CODE,
	P.PF_NAME,
	'大学院' AS KIND,
	5 AS OKEY,
	SUM(GRADUATE) AS AMT
FROM
	ENROLLMENT_STATUS S
INNER JOIN
    PREFECTURE P
ON
    S.PF_CODE = P.PF_CODE
WHERE
	S.SURVEY_YEAR = 2020
GROUP BY
    S.SURVEY_YEAR, P.PF_CODE, P.PF_NAME
HAVING
    AMT IS NOT NULL
) U
ORDER BY 
	PF_CODE ASC,
	OKEY ASC