SELECT
    SURVEY_YEAR AS SV_YEAR
    , PREFECTURE.PF_NAME AS PREFECTURE
    , CASE
        WHEN ELEMENTARY > 0 THEN '小学校'
        WHEN MIDDLE > 0 THEN '中学校'
        WHEN HIGH > 0 THEN '高校'
        WHEN JUNIOR_CLG > 0 THEN '短大'
        WHEN COLLEGE > 0 THEN '大学'
        WHEN GRADUATE > 0 THEN '大学院'
    END AS KIND
    , CASE
        WHEN ELEMENTARY > 0 THEN ELEMENTARY
        WHEN MIDDLE > 0 THEN MIDDLE
        WHEN HIGH > 0 THEN HIGH
        WHEN JUNIOR_CLG > 0 THEN JUNIOR_CLG
        WHEN COLLEGE > 0 THEN COLLEGE
        WHEN GRADUATE > 0 THEN GRADUATE
    END AS AMT
    
FROM
(
SELECT
    MAX(SURVEY_YEAR) AS SURVEY_YEAR
    , PF_CODE
    , SUM(IFNULL(ELEMENTARY, 0)) OVER
        (
            ORDER BY ELEMENTARY
        ) ELEMENTARY
    , SUM(IFNULL(MIDDLE, 0)) OVER
        (
            ORDER BY MIDDLE
        ) MIDDLE
    , SUM(IFNULL(HIGH, 0)) OVER
        (
            ORDER BY HIGH
        ) HIGH
    , SUM(IFNULL(JUNIOR_CLG, 0)) OVER
        (
            ORDER BY JUNIOR_CLG
        ) JUNIOR_CLG
    , SUM(IFNULL(COLLEGE, 0)) OVER
        (
            ORDER BY COLLEGE
        ) COLLEGE
    , SUM(IFNULL(GRADUATE, 0)) OVER
        (
            ORDER BY GRADUATE
        ) GRADUATE
FROM
    ENROLLMENT_STATUS
WHERE
    SURVEY_YEAR = 2020
GROUP BY
    PF_CODE
) ES
INNER JOIN PREFECTURE
    ON ES.PF_CODE = PREFECTURE.PF_CODE
ORDER BY 
    ES.PF_CODE
    , ES.ELEMENTARY DESC
    , ES.MIDDLE DESC
    , ES.HIGH DESC
    , ES.JUNIOR_CLG DESC
    , ES.GRADUATE DESC