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