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