SELECT
       2020 AS SV_YEAR
      ,MAX(PR.PF_NAME) AS PREFECTURE
      ,MAIN.KIND AS KIND
      ,SUM(MAIN.AMT)  AS AMT
FROM (
SELECT 
    PF_CODE
   ,1 AS KEY
   , '小学生' AS KIND
   ,SUM(ELEMENTARY) AS AMT
FROM ENROLLMENT_STATUS AS ES
WHERE SURVEY_YEAR = 2020
AND   ELEMENTARY IS NOT NULL
GROUP BY 
ES.PF_CODE
UNION ALL
SELECT 
    PF_CODE
   ,2 AS KEY
   , '中学生' AS KIND
   ,SUM(MIDDLE) AS AMT
FROM ENROLLMENT_STATUS AS ES
WHERE SURVEY_YEAR = 2020
AND   MIDDLE IS NOT NULL
GROUP BY 
ES.PF_CODE
UNION ALL
SELECT 
    PF_CODE
   ,3 AS KEY
   , '高校生' AS KIND
   ,SUM(HIGH) AS AMT
FROM ENROLLMENT_STATUS AS ES
WHERE SURVEY_YEAR = 2020
AND   HIGH IS NOT NULL
GROUP BY 
ES.PF_CODE
UNION ALL
SELECT 
    PF_CODE
   ,4 AS KEY
   , '短大' AS KIND
   ,SUM(JUNIOR_CLG) AS AMT
FROM ENROLLMENT_STATUS AS ES
WHERE SURVEY_YEAR = 2020
AND   JUNIOR_CLG IS NOT NULL
GROUP BY 
ES.PF_CODE
UNION ALL
SELECT 
    PF_CODE
   ,5 AS KEY
   , '大学' AS KIND
   ,SUM(COLLEGE) AS AMT
FROM ENROLLMENT_STATUS AS ES
WHERE SURVEY_YEAR = 2020
AND   COLLEGE IS NOT NULL
GROUP BY 
ES.PF_CODE
UNION ALL
SELECT 
    PF_CODE
   ,6 AS KEY
   , '大学院' AS KIND
   ,SUM(GRADUATE) AS AMT
FROM ENROLLMENT_STATUS AS ES
WHERE SURVEY_YEAR = 2020
AND   GRADUATE IS NOT NULL
GROUP BY 
ES.PF_CODE
) AS MAIN
LEFT JOIN PREFECTURE AS PR
ON MAIN.PF_CODE = PR.PF_CODE
GROUP BY KIND
ORDER BY MAIN.PF_CODE
        ,MAIN.KEY