WITH TMP AS(
SELECT
    E.SURVEY_YEAR AS SV_YEAR
    ,E.PF_CODE
    ,P.PF_NAME AS PREFECTURE
    ,SUM(E.ELEMENTARY) AS ELEMENTARY
    ,SUM(E.MIDDLE) AS MIDDLE
    ,SUM(E.HIGH) AS HIGH
    ,SUM(E.JUNIOR_CLG) AS JUNIOR_CLG
    ,SUM(E.COLLEGE) AS COLLEGE
    ,SUM(E.GRADUATE) AS GRADUATE
FROM
    ENROLLMENT_STATUS AS E
JOIN
    PREFECTURE AS P
ON
    E.PF_CODE = P.PF_CODE
WHERE
    E.SURVEY_YEAR = 2020
GROUP BY
    E.PF_CODE
    ,P.PF_NAME
)
,TMP2 AS(
SELECT SV_YEAR, PF_CODE, PREFECTURE, ELEMENTARY AS AMT, "小学校" AS KIND, 0 AS KIND_CODE FROM TMP
UNION ALL
SELECT SV_YEAR, PF_CODE, PREFECTURE, MIDDLE, "中学校", 1 FROM TMP
UNION ALL
SELECT SV_YEAR, PF_CODE, PREFECTURE, HIGH, "高校", 2 FROM TMP
UNION ALL
SELECT SV_YEAR, PF_CODE, PREFECTURE, JUNIOR_CLG, "短大", 3 FROM TMP
UNION ALL
SELECT SV_YEAR, PF_CODE, PREFECTURE, COLLEGE, "大学", 4 FROM TMP
UNION ALL
SELECT SV_YEAR, PF_CODE, PREFECTURE, GRADUATE, "大学院", 5 FROM TMP  
)

SELECT
    SV_YEAR
    ,PREFECTURE
    ,KIND
    ,AMT
FROM
    TMP2
WHERE
    TRUE 
    AND AMT IS NOT NULL
ORDER BY
    PF_CODE ASC
    ,KIND_CODE ASC