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