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