WITH SCHOOLS_TBL AS (
    SELECT
        1 AS SCHOOL
    UNION ALL
    SELECT
        SCHOOL + 1
    FROM
        SCHOOLS_TBL
    WHERE
        SCHOOL < 6
)
SELECT
    T.SURVEY_YEAR AS SV_YEAR,
    P.PF_NAME AS PREFECTURE,
    T.SCHOOL_LVL AS KIND,
    SUM(T.SCHOOL_AMT) AS AMT
FROM (
SELECT
    E.SURVEY_YEAR,
    E.PF_CODE,
    S.SCHOOL,
    CASE S.SCHOOL
        WHEN 1 THEN '小学校'
        WHEN 2 THEN '中学校'
        WHEN 3 THEN '高校'
        WHEN 4 THEN '短大'
        WHEN 5 THEN '大学'
        WHEN 6 THEN '大学院'
    END AS SCHOOL_LVL,
    CASE S.SCHOOL
        WHEN 1 THEN E.ELEMENTARY
        WHEN 2 THEN E.MIDDLE
        WHEN 3 THEN E.HIGH
        WHEN 4 THEN E.JUNIOR_CLG
        WHEN 5 THEN E.COLLEGE
        WHEN 6 THEN E.GRADUATE
    END AS SCHOOL_AMT
FROM
    ENROLLMENT_STATUS E
CROSS JOIN
    SCHOOLS_TBL S
WHERE
    E.SURVEY_YEAR = '2020'
) T
LEFT OUTER JOIN PREFECTURE P
ON T.PF_CODE = P.PF_CODE
WHERE
    T.SCHOOL_AMT IS NOT NULL
GROUP BY
    T.SURVEY_YEAR,
    P.PF_NAME,
    T.SCHOOL_LVL
ORDER BY
    P.PF_CODE ASC,
    T.SCHOOL ASC