WITH WO_AGE AS(
    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 SURVEY_YEAR, PF_CODE
)
, TEM AS(
    SELECT
        SURVEY_YEAR
        , PF_CODE
        , ELEMENTARY AS AMT
        , '小学校' AS KIND
        , 1 AS LEVEL
    FROM WO_AGE
    WHERE ELEMENTARY IS NOT NULL
    UNION ALL
    SELECT
        SURVEY_YEAR
        , PF_CODE
        , MIDDLE AS AMT
        , '中学校' AS KIND
        , 2 AS LEVEL
    FROM WO_AGE
    WHERE MIDDLE IS NOT NULL
    UNION ALL
    SELECT
        SURVEY_YEAR
        , PF_CODE
        , HIGH AS AMT
        , '高校' AS KIND
        , 3 AS LEVEL
    FROM WO_AGE
    WHERE HIGH IS NOT NULL
    UNION ALL
    SELECT
        SURVEY_YEAR
        , PF_CODE
        , JUNIOR_CLG AS AMT
        , '短大' AS KIND
        , 4 AS LEVEL
    FROM WO_AGE
    WHERE JUNIOR_CLG IS NOT NULL
    UNION ALL
    SELECT
        SURVEY_YEAR
        , PF_CODE
        , COLLEGE AS AMT
        , '大学' AS KIND
        , 5 AS LEVEL
    FROM WO_AGE
    WHERE COLLEGE IS NOT NULL
    UNION ALL
    SELECT
        SURVEY_YEAR
        , PF_CODE
        , GRADUATE AS AMT
        , '大学院' AS KIND
        , 6 AS LEVEL
    FROM WO_AGE
    WHERE GRADUATE IS NOT NULL
)

SELECT
    SURVEY_YEAR AS SV_YEAR
    , PF_NAME AS PREFECTURE
    , KIND
    , AMT
FROM TEM OUTER LEFT JOIN PREFECTURE P
    ON TEM.PF_CODE = P.PF_CODE
ORDER BY TEM.PF_CODE ASC, LEVEL ASC