WITH a AS (
    SELECT
        SURVEY_YEAR,
        PF_CODE,
        SUM(COALESCE(ELEMENTARY, 0)) AS 小学校,
        SUM(COALESCE(MIDDLE, 0)) AS 中学校,
        SUM(COALESCE(HIGH, 0)) AS 高校,
        SUM(COALESCE(JUNIOR_CLG, 0)) AS 短大,
        SUM(COALESCE(COLLEGE, 0)) AS 大学,
        SUM(COALESCE(GRADUATE, 0)) AS 大学院
    FROM
        ENROLLMENT_STATUS
    WHERE
        SURVEY_YEAR = '2020'
    GROUP BY
        PF_CODE, SURVEY_YEAR
)
SELECT *
FROM (
    SELECT
        a.SURVEY_YEAR AS SV_YEAR,
        p.PF_NAME AS PREFECTURE,
        '小学校' AS KIND,
        a.小学校 AS AMT
    FROM
        a
        INNER JOIN PREFECTURE p 
        ON p.PF_CODE = a.PF_CODE
    WHERE
        a.小学校 != 0
    UNION ALL
    SELECT
        a.SURVEY_YEAR AS SV_YEAR,
        p.PF_NAME AS PREFECTURE,
        '中学校' AS KIND,
        a.中学校 AS AMT
    FROM
        a
        INNER JOIN PREFECTURE p 
        ON p.PF_CODE = a.PF_CODE
    WHERE
        a.中学校 <> 0
    UNION ALL
    SELECT
        a.SURVEY_YEAR AS SV_YEAR,
        p.PF_NAME AS PREFECTURE,
        '高校' AS KIND,
        a.高校 AS AMT
    FROM
        a
        INNER JOIN PREFECTURE p 
        ON p.PF_CODE = a.PF_CODE
    WHERE
        a.高校 <> 0
    UNION ALL
    SELECT
        a.SURVEY_YEAR AS SV_YEAR,
        p.PF_NAME AS PREFECTURE,
        '短大' AS KIND,
        a.短大 AS AMT
    FROM
        a
        INNER JOIN PREFECTURE p 
        ON p.PF_CODE = a.PF_CODE
    WHERE
        a.短大 <> 0
    UNION ALL
    SELECT
        a.SURVEY_YEAR AS SV_YEAR,
        p.PF_NAME AS PREFECTURE,
        '大学' AS KIND,
        a.大学 AS AMT
    FROM
        a
        INNER JOIN PREFECTURE p 
        ON p.PF_CODE = a.PF_CODE
    WHERE
        a.大学 <> 0
    UNION ALL
    SELECT
        a.SURVEY_YEAR AS SV_YEAR,
        p.PF_NAME AS PREFECTURE,
        '大学院' AS KIND,
        a.大学院 AS AMT
    FROM
        a
        INNER JOIN PREFECTURE p 
        ON p.PF_CODE = a.PF_CODE
    WHERE
        a.大学院 <> 0
)
ORDER BY
    PREFECTURE,
    CASE KIND
        WHEN '小学校' THEN 1
        WHEN '中学校' THEN 2
        WHEN '高校' THEN 3
        WHEN '短大' THEN 4
        WHEN '大学' THEN 5
        WHEN '大学院' THEN 6
    END;