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