WITH
    ES2 AS (
        SELECT
            ES.SURVEY_YEAR,
            ES.PF_CODE,
            PF.PF_NAME,
            SUM(ELEMENTARY) AS 'EMT_AMT',
            SUM(MIDDLE) AS 'MD_AMT',
            SUM(HIGH) AS 'HIGH_AMT',
            SUM(JUNIOR_CLG) AS 'JC_AMT',
            SUM(COLLEGE) AS 'CL_AMT',
            SUM(GRADUATE) AS 'GD_AMT'
        FROM
            ENROLLMENT_STATUS AS ES
            INNER JOIN PREFECTURE AS PF ON ES.PF_CODE = PF.PF_CODE
        WHERE
            SURVEY_YEAR = 2020
        GROUP BY
            PF.PF_CODE
    )
SELECT
    ES2.SURVEY_YEAR AS SV_YEAR,
    ES2.PF_NAME AS PREFECTURE,
    c.KIND AS KIND,
    CASE c.KIND
        WHEN '小学校' THEN EMT_AMT
        WHEN '中学校' THEN MD_AMT
        WHEN '高校' THEN HIGH_AMT
        WHEN '短大' THEN JC_AMT
        WHEN '大学' THEN CL_AMT
        WHEN '大学院' THEN GD_AMT
        ELSE 0
    END AS AMT
FROM
    ES2
    CROSS JOIN (
        SELECT
            1 AS NUM,
            '小学校' AS KIND
        UNION ALL
        SELECT
            2 AS NUM,
            '中学校' AS KIND
        UNION ALL
        SELECT
            3 AS NUM,
            '高校' AS KIND
        UNION ALL
        SELECT
            4 AS NUM,
            '短大' AS KIND
        UNION ALL
        SELECT
            5 AS NUM,
            '大学' AS KIND
        UNION ALL
        SELECT
            6 AS NUM,
            '大学院' AS KIND
    ) AS c
WHERE
    AMT IS NOT NULL
ORDER BY
    ES2.PF_CODE ASC,
    c.NUM ASC;