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