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

ORDER BY
    MAIN.PF_CODE
    , VIEW