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

SELECT SV_YEAR
       ,PREFECTURE
       ,KIND
       ,AMT
FROM k
ORDER BY PF_CODE asc,KIND_NUMBER asc;