WITH SUBUNION AS (
    SELECT
        SURVEY_YEAR
      , PF_CODE
      , '小学校'                   AS "KIND"
      , SUM(NULLIF(ELEMENTARY, 0)) 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(NULLIF(MIDDLE, 0))     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(NULLIF(HIGH, 0))       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(NULLIF(JUNIOR_CLG, 0)) 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(NULLIF(COLLEGE, 0))    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(NULLIF(GRADUATE, 0))   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
;