WITH temp AS (
    SELECT
        es.SURVEY_YEAR AS SV_YEAR
        , es.PF_CODE
        , pf.PF_NAME AS PREFECTURE
        , SUM(es.ELEMENTARY) AS SUM_ELEMENTARY
        , SUM(es.MIDDLE) AS SUM_MIDDLE
        , SUM(es.HIGH) AS SUM_HIGH
        , SUM(es.JUNIOR_CLG) AS SUM_JUNIOR_CLG
        , SUM(es.COLLEGE) AS SUM_COLLEGE
        , SUM(es.GRADUATE) AS SUM_GRADUATE
    FROM
        ENROLLMENT_STATUS AS es
    INNER JOIN
        PREFECTURE AS pf
    ON
        es.PF_CODE = pf.PF_CODE
    WHERE
        SURVEY_YEAR = 2020
    GROUP BY
        es.PF_CODE, es.SURVEY_YEAR
)

SELECT
    SV_YEAR
    , PREFECTURE
    , KIND
    , AMT
FROM
    (
    SELECT SV_YEAR, PF_CODE, PREFECTURE, 1 AS KIND_CODE, '小学校' AS KIND, SUM_ELEMENTARY AS AMT FROM temp
    UNION
    SELECT SV_YEAR, PF_CODE, PREFECTURE, 2 AS KIND_CODE, '中学校' AS KIND, SUM_MIDDLE AS AMT FROM temp
    UNION
    SELECT SV_YEAR, PF_CODE, PREFECTURE, 3 AS KIND_CODE, '高校' AS KIND, SUM_HIGH AS AMT FROM temp
    UNION
    SELECT SV_YEAR, PF_CODE, PREFECTURE, 4 AS KIND_CODE, '短大' AS KIND, SUM_JUNIOR_CLG AS AMT FROM temp
    UNION
    SELECT SV_YEAR, PF_CODE, PREFECTURE, 5 AS KIND_CODE, '大学' AS KIND, SUM_COLLEGE AS AMT FROM temp
    UNION
    SELECT SV_YEAR, PF_CODE, PREFECTURE, 6 AS KIND_CODE, '大学院' AS KIND, SUM_GRADUATE AS AMT FROM temp
    )
WHERE
    AMT IS NOT NULL
ORDER BY
    PF_CODE ASC, KIND_CODE ASC
;