WITH sum_kind_amt AS (
    SELECT
        SURVEY_YEAR
        , PF_CODE
        , SUM(IFNULL(ELEMENTARY, 0)) AS ele
        , SUM(IFNULL(MIDDLE, 0)) AS mid
        , SUM(IFNULL(HIGH, 0)) AS hig
        , SUM(IFNULL(JUNIOR_CLG, 0)) AS jun
        , SUM(IFNULL(COLLEGE, 0)) AS col
        , SUM(IFNULL(GRADUATE, 0)) AS gra
    FROM
        ENROLLMENT_STATUS
    WHERE
        SURVEY_YEAR = 2020
    GROUP BY
        PF_CODE
)
, ele AS (
    SELECT
        SURVEY_YEAR
        , PF_CODE
        , CASE WHEN ele > 0 THEN '小学校' END AS kind
        , ele
    FROM sum_kind_amt
    WHERE ele > 0
)
, mid AS (
    SELECT
        SURVEY_YEAR
        , PF_CODE
        , CASE WHEN mid > 0 THEN '中学校' END AS kind
        , mid
    FROM sum_kind_amt
    WHERE mid > 0
)
, hig AS (
    SELECT
        SURVEY_YEAR
        , PF_CODE
        , CASE WHEN hig > 0 THEN '高校' END AS kind
        , hig
    FROM sum_kind_amt
    WHERE hig > 0
)
, jun AS (
    SELECT
        SURVEY_YEAR
        , PF_CODE
        , CASE WHEN jun > 0 THEN '短大' END AS kind
        , jun
    FROM sum_kind_amt
    WHERE jun > 0
)
, col AS (
    SELECT
        SURVEY_YEAR
        , PF_CODE
        , CASE WHEN col > 0 THEN '大学' END AS kind
        , col
    FROM sum_kind_amt
    WHERE col > 0
)
, gra AS (
    SELECT
        SURVEY_YEAR
        , PF_CODE
        , CASE WHEN gra > 0 THEN '大学院' END AS kind
        , gra
    FROM sum_kind_amt
    WHERE gra > 0
)
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
;