WITH SEQ_TBL AS ( 
    SELECT
        1 AS SEQ_NO 
    UNION ALL 
    SELECT
        SEQ_NO + 1 
    FROM
        SEQ_TBL 
    WHERE
        SEQ_NO < 6
)
SELECT 
    SUB.SURVEY_YEAR as SV_YEAR
    ,PF.PF_NAME as PREFECTURE
    ,SUB.KIND
    ,SUM(SUB.AMOUNT) as AMT
FROM
    (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) AS SUB
    INNER JOIN PREFECTURE AS PF
        ON SUB.PF_CODE = PF.PF_CODE
WHERE 
    SUB.AMOUNT IS NOT NULL
GROUP BY 
    SUB.SURVEY_YEAR
    ,PF.PF_NAME
    ,SUB.KIND
ORDER BY
    SUB.PF_CODE ASC
    ,SUB.SEQ_NO ASC