WITH TMP AS (
    SELECT
        PF_CODE,
        PF_NAME,
        KIND
    FROM
        PREFECTURE
    CROSS JOIN (
        SELECT '小学校' AS KIND
        UNION ALL
        SELECT '中学校' AS KIND
        UNION ALL
        SELECT '高校' AS KIND
        UNION ALL
        SELECT '短大' AS KIND
        UNION ALL
        SELECT '大学' AS KIND
        UNION ALL
        SELECT '大学院' AS KIND
    )
)
SELECT
    SV_YEAR,
    PREFECTURE,
    KIND,
    AMT
FROM (
    SELECT
        SURVEY_YEAR AS SV_YEAR,
        PF_CODE,
        PF_NAME AS PREFECTURE,
        KIND,
        CASE KIND
            WHEN '小学校' THEN ELEMENTARY
            WHEN '中学校' THEN MIDDLE
            WHEN '高校' THEN HIGH
            WHEN '短大' THEN JUNIOR_CLG
            WHEN '大学' THEN COLLEGE
            WHEN '大学院' THEN GRADUATE
        END AS AMT
    FROM
        TMP
    INNER JOIN (
        SELECT
            SURVEY_YEAR,
            PF_CODE,
            SUM(ELEMENTARY) AS ELEMENTARY,
            SUM(MIDDLE) AS MIDDLE,
            SUM(HIGH) AS HIGH,
            SUM(JUNIOR_CLG) AS JUNIOR_CLG,
            SUM(COLLEGE) AS COLLEGE,
            SUM(GRADUATE) AS GRADUATE
        FROM
            ENROLLMENT_STATUS
        WHERE
            SURVEY_YEAR = 2020
        GROUP BY
            PF_CODE
    ) USING (PF_CODE)
)
WHERE
    AMT IS NOT NULL
ORDER BY
    PF_CODE ASC
;