WITH es_X AS
(
    SELECT
        es.PF_CODE
        ,sum(ELEMENTARY) AS s_ELEMENTARY
        ,sum(MIDDLE) AS s_MIDDLE
        ,sum(HIGH) AS s_HIGH
        ,sum(JUNIOR_CLG) AS s_JUNIOR_CLG
        ,sum(COLLEGE) AS s_COLLEGE
        ,sum(GRADUATE) AS s_GRADUATE
    FROM
        ENROLLMENT_STATUS es
    WHERE
        es.SURVEY_YEAR = 2020
    GROUP BY
        es.PF_CODE
)
SELECT
    '2020' AS SV_YEAR
    ,pr.PF_NAME AS PREFECTURE
    ,X.KIND
    ,X.AMT
FROM (
SELECT
    es_X.PF_CODE
    ,'小学校' AS KIND
    ,s_ELEMENTARY AS AMT
FROM es_X
WHERE es_X.s_ELEMENTARY IS NOT NULL
UNION
SELECT
    es_X.PF_CODE
    ,'中学校' AS KIND
    ,s_MIDDLE AS AMT
FROM es_X
WHERE es_X.s_MIDDLE IS NOT NULL
UNION
SELECT
    es_X.PF_CODE
    ,'高校' AS KIND
    ,s_HIGH AS AMT
FROM es_X
WHERE es_X.s_HIGH IS NOT NULL
UNION
SELECT
    es_X.PF_CODE
    ,'短大' AS KIND
    ,s_JUNIOR_CLG AS AMT
FROM es_X
WHERE es_X.s_JUNIOR_CLG IS NOT NULL
UNION
SELECT
    es_X.PF_CODE
    ,'大学' AS KIND
    ,s_COLLEGE AS AMT
FROM es_X
WHERE es_X.s_COLLEGE IS NOT NULL
UNION
SELECT
    es_X.PF_CODE
    ,'大学院' AS KIND
    ,s_GRADUATE AS AMT
FROM es_X
WHERE es_X.s_GRADUATE IS NOT NULL
) X
    INNER JOIN
        PREFECTURE pr
        ON X.PF_CODE = pr.PF_CODE
ORDER BY
    X.PF_CODE
    ,CASE WHEN KIND = '小学校' THEN 1
          WHEN KIND = '中学校' THEN 2
          WHEN KIND = '高校' THEN 3
          WHEN KIND = '短大' THEN 4
          WHEN KIND = '大学' THEN 5
          WHEN KIND = '大学院' THEN 6 END