WITH JOINED AS (
    SELECT
        *
    FROM
        ENROLLMENT_STATUS AS ES
        INNER JOIN PREFECTURE AS PF ON ES.PF_CODE = PF.PF_CODE
    WHERE
        ES.SURVEY_YEAR = 2020
)
SELECT
    SURVEY_YEAR AS SV_YEAR,
    PF_NAME AS PREFECTURE,
    KIND,
    AMT
FROM
    (
        SELECT
            PF_CODE,
            SURVEY_YEAR,
            PF_NAME,
            '小学校' AS KIND,
            SUM(ELEMENTARY) AS AMT
        FROM
            JOINED
        GROUP BY
            PF_CODE
        UNION
        ALL
        SELECT
            PF_CODE,
            SURVEY_YEAR,
            PF_NAME,
            '中学校' AS KIND,
            SUM(MIDDLE) AS AMT
        FROM
            JOINED
        GROUP BY
            PF_CODE
        UNION
        ALL
        SELECT
            PF_CODE,
            SURVEY_YEAR,
            PF_NAME,
            '高校' AS KIND,
            SUM(HIGH) AS AMT
        FROM
            JOINED
        GROUP BY
            PF_CODE
        UNION
        ALL
        SELECT
            PF_CODE,
            SURVEY_YEAR,
            PF_NAME,
            '短大' AS KIND,
            SUM(JUNIOR_CLG) AS AMT
        FROM
            JOINED
        GROUP BY
            PF_CODE
        UNION
        ALL
        SELECT
            PF_CODE,
            SURVEY_YEAR,
            PF_NAME,
            '大学' AS KIND,
            SUM(COLLEGE) AS AMT
        FROM
            JOINED
        GROUP BY
            PF_CODE
        UNION
        ALL
        SELECT
            PF_CODE,
            SURVEY_YEAR,
            PF_NAME,
            '大学院' AS KIND,
            SUM(GRADUATE) AS AMT
        FROM
            JOINED
        GROUP BY
            PF_CODE
    )
WHERE
    AMT IS NOT NULL
ORDER BY
    PF_CODE