with k as( SELECT SURVEY_YEAR AS SV_YEAR 
                  ,ES.PF_CODE AS PF_CODE 
                  ,PR.PF_NAME AS PREFECTURE
                  ,'小学校' AS KIND
                  ,1 AS KINDNUMBER
                  ,SUM(ELEMENTARY) AS AMT
             FROM ENROLLMENT_STATUS AS ES
             LEFT JOIN PREFECTURE AS PR
             ON ES.PF_CODE = PR.PF_CODE
             WHERE ELEMENTARY IS NOT NULL
             AND SV_YEAR = '2020'
             GROUP BY ES.PF_CODE 
             union all
             SELECT SURVEY_YEAR AS SV_YEAR 
                  ,ES.PF_CODE AS PF_CODE 
                  ,PR.PF_NAME AS PREFECTURE
                  ,'中学校' AS KIND
                  ,2 AS KINDNUMBER
                  ,SUM(MIDDLE) AS AMT
             FROM ENROLLMENT_STATUS AS ES
             LEFT JOIN PREFECTURE AS PR
             ON ES.PF_CODE = PR.PF_CODE
             WHERE MIDDLE IS NOT NULL
             AND SV_YEAR = '2020'
             GROUP BY ES.PF_CODE 
             union all
             SELECT SURVEY_YEAR AS SV_YEAR 
                  ,ES.PF_CODE AS PF_CODE 
                  ,PR.PF_NAME AS PREFECTURE
                  ,'高校' AS KIND
                  ,3 AS KINDNUMBER
                  ,SUM(HIGH) AS AMT
             FROM ENROLLMENT_STATUS AS ES
             LEFT JOIN PREFECTURE AS PR
             ON ES.PF_CODE = PR.PF_CODE
             WHERE HIGH IS NOT NULL
             AND SV_YEAR = '2020'
             GROUP BY ES.PF_CODE 
             union all
             SELECT SURVEY_YEAR AS SV_YEAR 
                  ,ES.PF_CODE AS PF_CODE 
                  ,PR.PF_NAME AS PREFECTURE
                  ,'短大' AS KIND
                  ,4 AS KINDNUMBER
                  ,SUM(JUNIOR_CLG) AS AMT
             FROM ENROLLMENT_STATUS AS ES
             LEFT JOIN PREFECTURE AS PR
             ON ES.PF_CODE = PR.PF_CODE
             WHERE JUNIOR_CLG IS NOT NULL
             AND SV_YEAR = '2020'
             GROUP BY ES.PF_CODE 
             union all
             SELECT SURVEY_YEAR AS SV_YEAR 
                  ,ES.PF_CODE AS PF_CODE 
                  ,PR.PF_NAME AS PREFECTURE
                  ,'大学' AS KIND
                  ,5 AS KINDNUMBER
                  ,SUM(COLLEGE) AS AMT
             FROM ENROLLMENT_STATUS AS ES
             LEFT JOIN PREFECTURE AS PR
             ON ES.PF_CODE = PR.PF_CODE
             WHERE COLLEGE IS NOT NULL
             AND SV_YEAR = '2020'
             GROUP BY ES.PF_CODE 
             union all
             SELECT SURVEY_YEAR AS SV_YEAR 
                  ,ES.PF_CODE AS PF_CODE 
                  ,PR.PF_NAME AS PREFECTURE
                  ,'大学院' AS KIND
                  ,6 AS KINDNUMBER
                  ,SUM(GRADUATE) AS AMT
             FROM ENROLLMENT_STATUS AS ES
             LEFT JOIN PREFECTURE AS PR
             ON ES.PF_CODE = PR.PF_CODE
             WHERE GRADUATE IS NOT NULL
             AND SV_YEAR = '2020'
             GROUP BY ES.PF_CODE )
             
SELECT SV_YEAR AS SV_YEAR
       ,PREFECTURE AS PREFECTURE
       ,KIND AS KIND
       ,AMT AS AMT
FROM k

ORDER BY PF_CODE asc,KINDNUMBER asc