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