SELECT
    '2020' AS SV_YEAR,
    P.PF_NAME AS PREFECTURE,
    X.TYPE AS KIND,
    SUM(coalesce(X.AMT, 0)) AS AMT
FROM
    (
        SELECT
            PF_CODE,
            1 as b,
            '小学校' AS TYPE,
            ELEMENTARY AS AMT
        FROM
            ENROLLMENT_STATUS
        WHERE
            1 = 1
        UNION
        SELECT
            PF_CODE,
            2 as b,
            '中学校' AS TYPE,
            MIDDLE AS AMT
        FROM
            ENROLLMENT_STATUS
        WHERE
            1 = 1
            AND SURVEY_YEAR = 2020
        UNION
        SELECT
            PF_CODE,
            3 as b,
            '高校' AS TYPE,
            HIGH AS AMT
        FROM
            ENROLLMENT_STATUS
        WHERE
            1 = 1
            AND SURVEY_YEAR = 2020
        UNION
        SELECT
            PF_CODE,
            4 as b,
            '短大' AS TYPE,
            JUNIOR_CLG AS AMT
        FROM
            ENROLLMENT_STATUS
        WHERE
            1 = 1
            AND SURVEY_YEAR = 2020
        UNION
        SELECT
            PF_CODE,
            5 as b,
            '大学' AS TYPE,
            COLLEGE AS AMT
        FROM
            ENROLLMENT_STATUS
        WHERE
            1 = 1
            AND SURVEY_YEAR = 2020
        UNION
        SELECT
            PF_CODE,
            6 as b,
            '大学院' AS TYPE,
            GRADUATE AS AMT
        FROM
            ENROLLMENT_STATUS
        WHERE
            1 = 1
            AND SURVEY_YEAR = 2020
    ) AS X
LEFT JOIN
    PREFECTURE AS P
    ON 1=1
    AND X.PF_CODE = P.PF_CODE
GROUP BY
    X.PF_CODE,
    X.TYPE
HAVING
    SUM(coalesce(X.AMT, 0)) > 0
ORDER BY
    X.PF_CODE,
    X.b