WITH HORIZONTAL_SUM AS (
    SELECT
        -- *
        SURVEY_YEAR
        , PF_CODE
        -- SUMはnullは無視される。すべてnullの場合はnullになる。
        , 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
)
, VERTICAL AS (
    SELECT
        SURVEY_YEAR
        , PF_CODE
        , '小学校' AS KIND
        , ELEMENTARY AS AMT
    FROM
        HORIZONTAL_SUM
    
    UNION ALL
    SELECT
        SURVEY_YEAR
        , PF_CODE
        , '中学校' AS KIND
        , MIDDLE AS AMT
    FROM
        HORIZONTAL_SUM
    
    UNION ALL
    SELECT
        SURVEY_YEAR
        , PF_CODE
        , '高校' AS KIND
        , HIGH AS AMT
    FROM
        HORIZONTAL_SUM
    
    UNION ALL
    SELECT
        SURVEY_YEAR
        , PF_CODE
        , '短大' AS KIND
        , JUNIOR_CLG AS AMT
    FROM
        HORIZONTAL_SUM
    
    UNION ALL
    SELECT
        SURVEY_YEAR
        , PF_CODE
        , '大学' AS KIND
        , COLLEGE AS AMT
    FROM
        HORIZONTAL_SUM
    
    UNION ALL
    SELECT
        SURVEY_YEAR
        , PF_CODE
        , '大学院' AS KIND
        , GRADUATE AS AMT
    FROM
        HORIZONTAL_SUM
)
SELECT
    VERTICAL.SURVEY_YEAR AS SV_YEAR
    , PREFECTURE.PF_NAME AS PREFECTURE
    , VERTICAL.KIND
    , VERTICAL.AMT
FROM
    VERTICAL
INNER JOIN
    PREFECTURE ON PREFECTURE.PF_CODE = VERTICAL.PF_CODE
WHERE
    VERTICAL.AMT IS NOT NULL
ORDER BY
    VERTICAL.PF_CODE ASC
;