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