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