SELECT SV_YEAR, PREFECTURE, KIND, AMT FROM (SELECT E.SURVEY_YEAR AS SV_YEAR,
       P.PF_NAME AS PREFECTURE,
       '小学校' AS KIND,
       1 AS KIND_S,
       SUM(E.ELEMENTARY) AS AMT,
       E.PF_CODE AS CODE
FROM ENROLLMENT_STATUS E JOIN PREFECTURE P ON E.PF_CODE = P.PF_CODE
WHERE SV_YEAR = '2020' AND E.ELEMENTARY IS NOT NULL
GROUP BY E.SURVEY_YEAR, P.PF_NAME, E.PF_CODE

UNION ALL
SELECT E.SURVEY_YEAR AS SV_YEAR,
       P.PF_NAME AS PREFECTURE,
       '中学校' AS KIND,
       2 AS KIND_S,
       SUM(E.MIDDLE) AS AMT,
       E.PF_CODE AS CODE
FROM ENROLLMENT_STATUS E JOIN PREFECTURE P ON E.PF_CODE = P.PF_CODE
WHERE SV_YEAR = '2020' AND E.MIDDLE IS NOT NULL
GROUP BY E.SURVEY_YEAR, P.PF_NAME, E.PF_CODE


UNION ALL
SELECT E.SURVEY_YEAR AS SV_YEAR,
       P.PF_NAME AS PREFECTURE,
       '高校' AS KIND,
       3 AS KIND_S,
       SUM(E.HIGH) AS AMT,
       E.PF_CODE AS CODE
FROM ENROLLMENT_STATUS E JOIN PREFECTURE P ON E.PF_CODE = P.PF_CODE
WHERE SV_YEAR = '2020' AND E.HIGH IS NOT NULL
GROUP BY E.SURVEY_YEAR, P.PF_NAME, E.PF_CODE


UNION ALL
SELECT E.SURVEY_YEAR AS SV_YEAR,
       P.PF_NAME AS PREFECTURE,
       '短大' AS KIND,
       4 AS KIND_S,
       SUM(E.JUNIOR_CLG) AS AMT,
       E.PF_CODE AS CODE
FROM ENROLLMENT_STATUS E JOIN PREFECTURE P ON E.PF_CODE = P.PF_CODE
WHERE SV_YEAR = '2020' AND E.JUNIOR_CLG IS NOT NULL
GROUP BY E.SURVEY_YEAR, P.PF_NAME, E.PF_CODE


UNION ALL
SELECT E.SURVEY_YEAR AS SV_YEAR,
       P.PF_NAME AS PREFECTURE,
       '大学' AS KIND,
       5 AS KIND_S,
       SUM(E.COLLEGE) AS AMT,
       E.PF_CODE AS CODE
FROM ENROLLMENT_STATUS E JOIN PREFECTURE P ON E.PF_CODE = P.PF_CODE
WHERE SV_YEAR = '2020' AND E.COLLEGE IS NOT NULL
GROUP BY E.SURVEY_YEAR, P.PF_NAME, E.PF_CODE


UNION ALL
SELECT E.SURVEY_YEAR AS SV_YEAR,
       P.PF_NAME AS PREFECTURE,
       '大学院' AS KIND,
       6 AS KIND_S,
       SUM(E.GRADUATE) AS AMT,
       E.PF_CODE AS CODE
FROM ENROLLMENT_STATUS E JOIN PREFECTURE P ON E.PF_CODE = P.PF_CODE
WHERE SV_YEAR = '2020' AND E.GRADUATE IS NOT NULL
GROUP BY E.SURVEY_YEAR, P.PF_NAME, E.PF_CODE

ORDER BY CODE ASC, KIND_S ASC);