WITH temp AS (
    SELECT
        status.survey_year AS SV_YEAR
        , status.pf_code AS CODE
        , pref.pf_name AS PREFECTURE
        , SUM(elementary) AS elementary
        , SUM(middle) AS middle
        , SUM(high) AS high
        , SUM(junior_clg) AS junior_clg
        , SUM(college) AS college
        , SUM(GRADUATE) AS graduate
    FROM
        ENROLLMENT_STATUS AS status
        INNER JOIN
            PREFECTURE AS pref
            ON status.PF_CODE = pref.PF_CODE
    WHERE
        status.survey_year = 2020
    GROUP BY
        1, 2
)
SELECT
    SV_YEAR
    , PREFECTURE
    , KIND
    , AMT
FROM (
    SELECT SV_YEAR, CODE, PREFECTURE, '小学校' AS KIND, elementary AS AMT, 1 AS kind_id FROM temp WHERE AMT IS NOT NULL
    UNION ALL
    SELECT SV_YEAR, CODE, PREFECTURE, '中学校' AS KIND, middle AS AMT, 2 AS kind_id FROM temp WHERE AMT IS NOT NULL
    UNION ALL
    SELECT SV_YEAR, CODE, PREFECTURE, '高校' AS KIND, high AS AMT, 3 AS kind_id FROM temp WHERE AMT IS NOT NULL
    UNION ALL
    SELECT SV_YEAR, CODE, PREFECTURE, '短大' AS KIND, junior_clg AS AMT, 4 AS kind_id FROM temp WHERE AMT IS NOT NULL
    UNION ALL
    SELECT SV_YEAR, CODE, PREFECTURE, '大学' AS KIND, college AS AMT, 5 AS kind_id FROM temp WHERE AMT IS NOT NULL
    UNION ALL
    SELECT SV_YEAR, CODE, PREFECTURE, '大学院' AS KIND, graduate AS AMT, 6 AS kind_id FROM temp WHERE AMT IS NOT NULL
    ) x
ORDER BY
    CODE ASC
    , kind_id ASC