WITH SEQ_TBL AS(
    SELECT
        1 AS SEQ_NO
    UNION ALL 
    SELECT
        SEQ_NO + 1
    FROM
        SEQ_TBL
    WHERE
        SEQ_NO < 6
),CROSS_ES_SEQ AS(
    SELECT
        t1.SURVEY_YEAR,
        t1.PF_CODE,
        t2.SEQ_NO,
        CASE t2.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 t2.SEQ_NO
            WHEN 1 THEN t1.ELEMENTARY
            WHEN 2 THEN t1.MIDDLE	
            WHEN 3 THEN t1.HIGH
            WHEN 4 THEN t1.JUNIOR_CLG
            WHEN 5 THEN t1.COLLEGE
            WHEN 6 THEN t1.GRADUATE
        ELSE NULL
        END AS AMOUNT
    FROM
        ENROLLMENT_STATUS AS t1
        CROSS JOIN SEQ_TBL AS t2
    WHERE
        SURVEY_YEAR = 2020
)
SELECT
    t3.SURVEY_YEAR AS SV_YEAR,
    t4.PF_NAME AS PREFECTURE,
    t3.KIND,
    SUM(t3.AMOUNT) AS AMT
FROM
    CROSS_ES_SEQ AS t3
    INNER JOIN PREFECTURE AS t4
        ON t3.PF_CODE = t4.PF_CODE
WHERE
    t3.AMOUNT IS NOT NULL
GROUP BY
    SV_YEAR,
    PREFECTURE,
    KIND
ORDER BY
    t3.PF_CODE ASC,
    t3.SEQ_NO ASC