SELECT
    2020 AS SV_YEAR,
    p.PF_NAME AS PREFECTURE,
    z.KIND,
    SUM(z.AMT)
FROM PREFECTURE AS p
    INNER JOIN  ENROLLMENT_STATUS AS e ON p.PF_CODE = e.PF_CODE
    INNER JOIN 
    (SELECT e1.PF_CODE,SURVEY_YEAR, '小学校' AS KIND, e1.ELEMENTARY AS AMT,1 AS sort FROM ENROLLMENT_STATUS AS e1
    UNION ALL
    SELECT e2.PF_CODE,SURVEY_YEAR, '中学校' AS KIND, e2.MIDDLE AS AMT,2 AS sort FROM ENROLLMENT_STATUS AS e2
    UNION ALL
    SELECT e3.PF_CODE,SURVEY_YEAR, '高校' AS KIND, e3.HIGH AS AMT,3 AS sort FROM ENROLLMENT_STATUS AS e3
    UNION ALL
    SELECT e2.PF_CODE,SURVEY_YEAR, '短大' AS KIND, e2.JUNIOR_CLG AS AMT,4 AS sort FROM ENROLLMENT_STATUS AS e2
    UNION ALL
    SELECT e2.PF_CODE,SURVEY_YEAR, '大学' AS KIND, e2.COLLEGE AS AMT,5 AS sort FROM ENROLLMENT_STATUS AS e2
    UNION ALL
    SELECT e2.PF_CODE,SURVEY_YEAR, '大学院' AS KIND, e2.GRADUATE AS AMT,6 AS sort FROM ENROLLMENT_STATUS AS e2
    
    ) AS z ON e.PF_CODE = z.PF_CODE AND e.SURVEY_YEAR = z.SURVEY_YEAR
    
WHERE e.SURVEY_YEAR = 2020 AND AMT IS NOT NULL AND AMT <> 0
GROUP BY SV_YEAR, PREFECTURE, z.KIND
ORDER BY p.PF_CODE, z.sort