WITH SUM_TABLE AS (
    SELECT SURVEY_YEAR AS SV_YEAR,
        PF_CODE,
        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
    WHERE SURVEY_YEAR = 2020
    GROUP BY SURVEY_YEAR, PF_CODE
),
UNION_TABLE AS (
    SELECT SV_YEAR, PF_CODE, "小学校" AS KIND, 1 AS KIND_ORDER, ELEMENTARY AS AMT FROM SUM_TABLE
    UNION
    SELECT SV_YEAR, PF_CODE, "中学校" AS KIND, 2 AS KIND_ORDER, MIDDLE AS AMT FROM SUM_TABLE
    UNION
    SELECT SV_YEAR, PF_CODE, "高校" AS KIND, 3 AS KIND_ORDER, HIGH AS AMT FROM SUM_TABLE
    UNION
    SELECT SV_YEAR, PF_CODE, "短大" AS KIND, 4 AS KIND_ORDER, JUNIOR_CLG AS AMT FROM SUM_TABLE
    UNION
    SELECT SV_YEAR, PF_CODE, "大学" AS KIND, 5 AS KIND_ORDER, COLLEGE AS AMT FROM SUM_TABLE
    UNION
    SELECT SV_YEAR, PF_CODE, "大学院" AS KIND, 6 AS KIND_ORDER, GRADUATE AS AMT FROM SUM_TABLE
)
SELECT SV_YEAR, PF_NAME AS PREFECTURE, KIND, AMT
FROM UNION_TABLE AS A INNER JOIN PREFECTURE AS P ON A.PF_CODE = P.PF_CODE
WHERE AMT IS NOT NULL
ORDER BY P.PF_CODE, KIND_ORDER