WITH ENROLLMENT_STACK AS (
    SELECT
        SURVEY_YEAR AS SV_YEAR,
        PF_CODE,
        '小学校' AS KIND,
        SUM(ELEMENTARY) AS AMT
    FROM
        ENROLLMENT_STATUS
    WHERE
        SURVEY_YEAR = 2020
    GROUP BY
        SURVEY_YEAR,
        PF_CODE
    
    UNION ALL
    SELECT
        SURVEY_YEAR AS SV_YEAR,
        PF_CODE,
        '中学校' AS KIND,
        SUM(MIDDLE) AS AMT
    FROM
        ENROLLMENT_STATUS
    WHERE
        SURVEY_YEAR = 2020
    GROUP BY
        SURVEY_YEAR,
        PF_CODE
    
    UNION ALL
    SELECT
        SURVEY_YEAR AS SV_YEAR,
        PF_CODE,
        '高校' AS KIND,
        SUM(HIGH) AS AMT
    FROM
        ENROLLMENT_STATUS
    WHERE
        SURVEY_YEAR = 2020
    GROUP BY
        SURVEY_YEAR,
        PF_CODE
    
    UNION ALL
    SELECT
        SURVEY_YEAR AS SV_YEAR,
        PF_CODE,
        '短大' AS KIND,
        SUM(JUNIOR_CLG) AS AMT
    FROM
        ENROLLMENT_STATUS
    WHERE
        SURVEY_YEAR = 2020
    GROUP BY
        SURVEY_YEAR,
        PF_CODE
    
    UNION ALL
    SELECT
        SURVEY_YEAR AS SV_YEAR,
        PF_CODE,
        '大学' AS KIND,
        SUM(COLLEGE) AS AMT
    FROM
        ENROLLMENT_STATUS
    WHERE
        SURVEY_YEAR = 2020
    GROUP BY
        SURVEY_YEAR,
        PF_CODE
        
    UNION ALL
    SELECT
        SURVEY_YEAR AS SV_YEAR,
        PF_CODE,
        '大学院' AS KIND,
        SUM(GRADUATE) AS AMT
    FROM
        ENROLLMENT_STATUS
    WHERE
        SURVEY_YEAR = 2020
    GROUP BY
        SURVEY_YEAR,
        PF_CODE
)

SELECT
    SV_YEAR,
    PF_NAME,
    KIND,
    AMT
FROM
    ENROLLMENT_STACK E_S
    INNER JOIN PREFECTURE P
        ON P.PF_CODE = E_S.PF_CODE
WHERE
    AMT IS NOT NULL
ORDER BY
    P.PF_CODE ASC
;