WITH 
status AS(
SELECT
    SURVEY_YEAR,
    PF_CODE,
    PF_NAME,
    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 USING(PF_CODE)
WHERE
    SURVEY_YEAR = 2020
GROUP BY
    PF_CODE
)
SELECT
    SV_YEAR,
    PREFECTURE,
    KIND,
    AMT
FROM(
    SELECT SURVEY_YEAR AS SV_YEAR, PF_CODE, PF_NAME AS PREFECTURE, ELEMENTARY AS AMT, "小学校" AS KIND, 0 AS KIND_CODE FROM status UNION ALL
    SELECT SURVEY_YEAR, PF_CODE, PF_NAME, MIDDLE, "中学校", 1 FROM status UNION ALL
    SELECT SURVEY_YEAR, PF_CODE, PF_NAME, HIGH, "高校", 2 FROM status UNION ALL
    SELECT SURVEY_YEAR, PF_CODE, PF_NAME, JUNIOR_CLG, "短大", 3 FROM status UNION ALL
    SELECT SURVEY_YEAR, PF_CODE, PF_NAME, COLLEGE, "大学", 4 FROM status UNION ALL
    SELECT SURVEY_YEAR, PF_CODE, PF_NAME, GRADUATE, "大学院", 5 FROM status
)
WHERE TRUE
    AND AMT IS NOT NULL   
ORDER BY
    PF_CODE ASC,
    KIND_CODE ASC