WITH m1 AS(
    SELECT
        SURVEY_YEAR AS SV_YEAR
        , PF_NAME AS PREFECTURE
        , ENROLLMENT_STATUS.PF_CODE
        , SUM(ELEMENTARY) AS ELEMENTARY_SUM
        , SUM(MIDDLE) AS MIDDLE_SUM
        , SUM(HIGH) AS HIGH_SUM
        , SUM(JUNIOR_CLG) AS JUNIOR_CLG_SUM
        , SUM(COLLEGE) AS COLLEGE_SUM
        , SUM(GRADUATE) AS GRADUATE_SUM
    FROM
        ENROLLMENT_STATUS
        INNER JOIN
        PREFECTURE
        ON
        ENROLLMENT_STATUS.PF_CODE = PREFECTURE.PF_CODE
    WHERE
        SURVEY_YEAR = '2020'
    GROUP BY
        1, 2, 3
)
, m2 AS (

    SELECT
        SV_YEAR
        , PREFECTURE
        , PF_CODE
        , '小学校' AS KIND
        , ELEMENTARY_SUM AS AMT
    FROM
        m1
    UNION ALL
    SELECT
        SV_YEAR
        , PREFECTURE
        , PF_CODE
        , '中学校' AS KIND
        , MIDDLE_SUM AS AMT
    FROM
        m1
    UNION ALL
    SELECT
        SV_YEAR
        , PREFECTURE
        , PF_CODE
        , '高校' AS KIND
        , HIGH_SUM AS AMT
    FROM
        m1
    UNION ALL
    SELECT
        SV_YEAR
        , PREFECTURE
        , PF_CODE
        , '短大' AS KIND
        , JUNIOR_CLG_SUM AS AMT
    FROM
        m1
    UNION ALL
    SELECT
        SV_YEAR
        , PREFECTURE
        , PF_CODE
        , '大学' AS KIND
        , COLLEGE_SUM AS AMT
    FROM
        m1
    UNION ALL
    SELECT
        SV_YEAR
        , PREFECTURE
        , PF_CODE
        , '大学院' AS KIND
        , GRADUATE_SUM AS AMT
    FROM
        m1
)
SELECT
    SV_YEAR
    , PREFECTURE
    , KIND
    , AMT
FROM
    m2
WHERE
    AMT IS NOT NULL
ORDER BY
    PF_CODE
;