WITH sum_kind_amt AS (
    SELECT
        SURVEY_YEAR
        , PF_CODE
        -- IFNULLで置換しないままにすればnullが残る
        , SUM(ELEMENTARY) AS ele
        , SUM(MIDDLE) AS mid
        , SUM(HIGH) AS hig
        , SUM(JUNIOR_CLG) AS jun
        , SUM(COLLEGE) AS col
        , SUM(GRADUATE) AS gra

    FROM
        ENROLLMENT_STATUS
    WHERE
        SURVEY_YEAR = 2020
    GROUP BY
        PF_CODE
)
, ele AS (
    SELECT
        SURVEY_YEAR
        , PF_CODE
        , '小学校' AS kind
        , ele
    FROM sum_kind_amt
    WHERE ele IS NOT NULL
)
, mid AS (
    SELECT
        SURVEY_YEAR
        , PF_CODE
        , '中学校' AS kind
        , mid
    FROM sum_kind_amt
    WHERE mid IS NOT NULL
)
, hig AS (
    SELECT
        SURVEY_YEAR
        , PF_CODE
        , '高校' AS kind
        , hig
    FROM sum_kind_amt
    WHERE hig IS NOT NULL
)
, jun AS (
    SELECT
        SURVEY_YEAR
        , PF_CODE
        , '短大' AS kind
        , jun
    FROM sum_kind_amt
    WHERE jun IS NOT NULL
)
, col AS (
    SELECT
        SURVEY_YEAR
        , PF_CODE
        , '大学' AS kind
        , col
    FROM sum_kind_amt
    WHERE col IS NOT NULL
)
, gra AS (
    SELECT
        SURVEY_YEAR
        , PF_CODE
        , '大学院' AS kind
        , gra
    FROM sum_kind_amt
    WHERE gra IS NOT NULL
)

SELECT
    kind_amts.SURVEY_YEAR AS SV_YEAR
    -- , kind_amts.PF_CODE -- DEBUG
    , PREFECTURE.PF_NAME AS PREFECTURE
    , kind_amts.kind AS KIND
    , kind_amts.ele AS AMT
FROM
    (
        SELECT * FROM ele
        UNION ALL
        SELECT * FROM mid
        UNION ALL
        SELECT * FROM hig
        UNION ALL
        SELECT * FROM jun
        UNION ALL
        SELECT * FROM col
        UNION ALL
        SELECT * FROM gra
    ) AS kind_amts
INNER JOIN
    PREFECTURE ON kind_amts.PF_CODE = PREFECTURE.PF_CODE
ORDER BY
    kind_amts.PF_CODE ASC
    ,
    CASE kind_amts.kind 
        WHEN '小学校' THEN 1
        WHEN '中学校' THEN 2
        WHEN '高校' THEN 3
        WHEN '短大' THEN 4
        WHEN '大学' THEN 5
        WHEN '大学院' THEN 6
    END
;