WITH
es_e AS (
    SELECT
        SURVEY_YEAR, PF_CODE,
        '小学校' AS KIND,
        1 AS KIND_NO,
        SUM(ELEMENTARY) AS AMT
    FROM ENROLLMENT_STATUS
    WHERE SURVEY_YEAR = 2020 AND ELEMENTARY >= 0
    GROUP BY 1,2
),
es_m AS (
    SELECT
        SURVEY_YEAR, PF_CODE,
        '中学校' AS KIND,
        2 AS KIND_NO,
        SUM(MIDDLE) AS AMT
    FROM ENROLLMENT_STATUS
    WHERE SURVEY_YEAR = 2020 AND MIDDLE >= 0
    GROUP BY 1,2
),
es_h AS (
    SELECT
        SURVEY_YEAR, PF_CODE,
        '高校' AS KIND,
        3 AS KIND_NO,
        SUM(HIGH) AS AMT
    FROM ENROLLMENT_STATUS
    WHERE SURVEY_YEAR = 2020 AND HIGH >= 0
    GROUP BY 1,2
),
es_j AS (
    SELECT
        SURVEY_YEAR, PF_CODE,
        '短大' AS KIND,
        4 AS KIND_NO,
        SUM(JUNIOR_CLG) AS AMT
    FROM ENROLLMENT_STATUS
    WHERE SURVEY_YEAR = 2020 AND JUNIOR_CLG >= 0
    GROUP BY 1,2
),
es_c AS (
    SELECT
        SURVEY_YEAR, PF_CODE,
        '大学' AS KIND,
        5 AS KIND_NO,
        SUM(COLLEGE) AS AMT
    FROM ENROLLMENT_STATUS
    WHERE SURVEY_YEAR = 2020 AND COLLEGE >= 0
    GROUP BY 1,2
),
es_g AS (
    SELECT
        SURVEY_YEAR, PF_CODE,
        '大学院' AS KIND,
        6 AS KIND_NO,
        SUM(GRADUATE) AS AMT
    FROM ENROLLMENT_STATUS
    WHERE SURVEY_YEAR = 2020 AND GRADUATE >= 0
    GROUP BY 1,2
)
SELECT
    SURVEY_YEAR AS SV_YEAR,
    PF_NAME AS PREFECTURE,
    KIND,
    AMT
FROM (
    SELECT * FROM es_e
    UNION ALL
    SELECT * FROM es_m
    UNION ALL 
    SELECT * FROM es_h
    UNION ALL 
    SELECT * FROM es_j
    UNION ALL 
    SELECT * FROM es_c
    UNION ALL 
    SELECT * FROM es_g
) AS t INNER JOIN PREFECTURE USING(PF_CODE)
ORDER BY PF_CODE, KIND_NO