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