WITH KIND_ID AS(
    SELECT
        1 AS ID
    UNION ALL
    SELECT
        ID + 1
    FROM
        KIND_ID
    WHERE
        ID < 6
),
TMP_TABLE AS(
    SELECT
        SURVEY_YEAR AS SV_YEAR,
        PREFECTURE.PF_CODE,
        PREFECTURE.PF_NAME AS PREFECTURE,
        ID,
        CASE ID
            WHEN 1 THEN '小学校'
            WHEN 2 THEN '中学校'
            WHEN 3 THEN '高校'
            WHEN 4 THEN '短大'
            WHEN 5 THEN '大学'
            WHEN 6 THEN '大学院'
            ELSE NULL
        END AS KIND,
        CASE ID
            WHEN 1 THEN ELEMENTARY
            WHEN 2 THEN MIDDLE
            WHEN 3 THEN HIGH
            WHEN 4 THEN JUNIOR_CLG
            WHEN 5 THEN COLLEGE
            WHEN 6 THEN GRADUATE
            ELSE NULL
        END AS PARTIAL_AMT
    FROM
        ENROLLMENT_STATUS
        CROSS JOIN
            KIND_ID
        INNER JOIN
            PREFECTURE ON PREFECTURE.PF_CODE = ENROLLMENT_STATUS.PF_CODE
    WHERE
        SV_YEAR = '2020'
)
SELECT
    SV_YEAR,
    PREFECTURE,
    KIND,
    SUM(PARTIAL_AMT) AS AMT
FROM
    TMP_TABLE
WHERE
    PARTIAL_AMT IS NOT NULL
GROUP BY
    SV_YEAR,
    PREFECTURE,
    KIND
ORDER BY
    PF_CODE ASC,
    ID ASC