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,
    ES.AGE,
    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
),

MERGED_DATA AS (
    SELECT *
    FROM ES_SUB
    INNER JOIN PREFECTURE AS PF ON ES_SUB.PF_CODE = PF.PF_CODE
)

SELECT
    SURVEY_YEAR AS SV_YEAR,
    PF_NAME AS PREFECTURE,
    KIND,
    SUM(AMOUNT) AS AMT 
FROM MERGED_DATA
WHERE AMOUNT IS NOT NULL
GROUP BY SURVEY_YEAR, PF_NAME, KIND
ORDER BY PF_CODE ASC, SEQ_NO ASC