SELECT 

ES.SURVEY_YEAR AS SV_YEAR
,PR.PF_NAME AS PREFECTURE
,AB.KIND
,AB.AMT


FROM
ENROLLMENT_STATUS ES

LEFT OUTER JOIN 
(
SELECT 1 AS NU,ES.PF_CODE,'小学校' AS KIND,SUM(ES.ELEMENTARY) AS AMT FROM ENROLLMENT_STATUS ES WHERE ES.SURVEY_YEAR = 2020 AND ES.ELEMENTARY IS NOT NULL GROUP BY ES.PF_CODE
UNION ALL
SELECT 2 AS NU,ES.PF_CODE,'中学校' AS KIND ,SUM(ES.MIDDLE) AS AMT FROM ENROLLMENT_STATUS ES WHERE ES.SURVEY_YEAR = 2020  AND ES.MIDDLE IS NOT NULL GROUP BY ES.PF_CODE
UNION ALL
SELECT 3 AS NU,ES.PF_CODE,'高校' AS KIND,SUM(ES.HIGH) AS AMT FROM ENROLLMENT_STATUS ES WHERE ES.SURVEY_YEAR = 2020 AND ES.HIGH IS NOT NULL GROUP BY ES.PF_CODE
UNION ALL
SELECT 4 AS NU,ES.PF_CODE,'短大' AS KIND,SUM(ES.JUNIOR_CLG) AS AMT FROM ENROLLMENT_STATUS ES WHERE ES.SURVEY_YEAR = 2020 AND ES.JUNIOR_CLG IS NOT NULL GROUP BY ES.PF_CODE
UNION ALL
SELECT 5 AS NU,ES.PF_CODE,'大学' AS KIND,SUM(ES.COLLEGE) AS AMT FROM ENROLLMENT_STATUS ES WHERE ES.SURVEY_YEAR = 2020 AND ES.COLLEGE IS NOT NULL GROUP BY ES.PF_CODE
UNION ALL
SELECT 6 AS NU,ES.PF_CODE,'大学院' AS KIND,SUM(ES.GRADUATE) AS AMT FROM ENROLLMENT_STATUS ES WHERE ES.SURVEY_YEAR = 2020 AND ES.GRADUATE IS NOT NULL GROUP BY ES.PF_CODE
) AS AB

ON ES.PF_CODE = AB.PF_CODE

LEFT OUTER JOIN PREFECTURE PR
ON ES.PF_CODE = PR.PF_CODE

WHERE ES.SURVEY_YEAR = 2020

GROUP BY ES.SURVEY_YEAR,PR.PF_NAME,AB.KIND

ORDER BY ES.PF_CODE,AB.NU