WITH TMP AS(
    SELECT
        1 AS NO
    UNION ALL
    SELECT NO + 1
    FROM
        TMP
    WHERE
        NO < 6
)

SELECT
    TMP2.SURVEY_YEAR AS SV_YEAR,
    P.PF_NAME AS PREFECTURE,
    TMP2.KIND AS KIND,
    SUM(TMP2.AMT) AS AMT
FROM
    (SELECT
        ES.SURVEY_YEAR,
        ES.PF_CODE,
        T.NO,
        CASE T.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 T.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 AMT
    FROM
        ENROLLMENT_STATUS AS ES
        CROSS JOIN TMP AS T
    WHERE
        ES.SURVEY_YEAR = 2020
    ) AS TMP2
    
INNER JOIN PREFECTURE AS P
ON TMP2.PF_CODE = P.PF_CODE
WHERE
    TMP2.AMT IS NOT NULL
GROUP BY
    TMP2.SURVEY_YEAR,
    TMP2.PF_CODE,
    TMP2.KIND
ORDER BY
    TMP2.PF_CODE ASC,
    TMP2.NO ASC
   
;