WITH target AS (
    SELECT
        PF_CODE
        , ELEMENTARY AS AMT
        , '小学校' AS KIND
        , 1 AS sort_order
    FROM
        ENROLLMENT_STATUS
    WHERE
        SURVEY_YEAR = 2020
    UNION ALL
    SELECT
        PF_CODE
        , MIDDLE AS AMT
        , '中学校' AS KIND
        , 2 AS sort_order
    FROM
        ENROLLMENT_STATUS
    WHERE
        SURVEY_YEAR = 2020
    UNION ALL
    SELECT
        PF_CODE
        , HIGH AS AMT
        , '高校' AS KIND
        , 3 AS sort_order
    FROM
        ENROLLMENT_STATUS
    WHERE
        SURVEY_YEAR = 2020
    UNION ALL
    SELECT
        PF_CODE
        , JUNIOR_CLG AS AMT
        , '短大' AS KIND
        , 4 AS sort_order
    FROM
        ENROLLMENT_STATUS
    WHERE
        SURVEY_YEAR = 2020
    UNION ALL
    SELECT
        PF_CODE
        , COLLEGE AS AMT
        , '大学' AS KIND
        , 5 AS sort_order
    FROM
        ENROLLMENT_STATUS
    WHERE
        SURVEY_YEAR = 2020
    UNION ALL
    SELECT
        PF_CODE
        , GRADUATE AS AMT
        , '大学院' AS KIND
        , 6 AS sort_order
    FROM
        ENROLLMENT_STATUS
    WHERE
        SURVEY_YEAR = 2020
)
SELECT
    2020 AS SV_YEAR
    , PF_NAME AS PREFECTURE
    , KIND
    , sum(AMT) AS AMT
FROM
    target
    INNER JOIN PREFECTURE
        USING (PF_CODE)
WHERE
    AMT IS NOT NULL
GROUP BY
    SV_YEAR
    , PREFECTURE
    , KIND
ORDER BY
    PF_CODE
    , sort_order
;