WITH SUB AS(
    SELECT
        SURVEY_YEAR AS 'SV_YEAR'
        ,PF_NAME AS 'PREFECTURE'
        ,SUM(ELEMENTARY) SE
        ,SUM(MIDDLE) SM
        ,SUM(HIGH) SH
        ,SUM(JUNIOR_CLG) SJ
        ,SUM(COLLEGE) SC
        ,SUM(GRADUATE) SG

    FROM
        ENROLLMENT_STATUS AS ES
        INNER JOIN
            PREFECTURE AS P
            ON
            P.PF_CODE=ES.PF_CODE
    WHERE
        SV_YEAR='2020'
    GROUP BY 
        ES.PF_CODE
    ORDER BY
        ES.PF_CODE
   )
   
SELECT
    SV_YEAR
    ,PREFECTURE
    ,'小学校' AS KIND
    ,SE AS AMT
FROM SUB
WHERE AMT IS NOT NULL

UNION ALL
SELECT
    SV_YEAR
    ,PREFECTURE
    ,'中学校' AS KIND
    ,SM AS AMT
FROM SUB
WHERE AMT IS NOT NULL

UNION ALL
SELECT
    SV_YEAR
    ,PREFECTURE
    ,'高校' AS KIND
    ,SH AS AMT
FROM SUB
WHERE AMT IS NOT NULL

UNION ALL
SELECT
    SV_YEAR
    ,PREFECTURE
    ,'短大' AS KIND
    ,SJ AS AMT
FROM SUB
WHERE AMT IS NOT NULL

UNION ALL
SELECT
    SV_YEAR
    ,PREFECTURE
    ,'大学' AS KIND
    ,SC AS AMT
FROM SUB
WHERE AMT IS NOT NULL

UNION ALL
SELECT
    SV_YEAR
    ,PREFECTURE
    ,'大学院' AS KIND
    ,SG AS AMT
FROM SUB
WHERE AMT IS NOT NULL

ORDER BY PREFECTURE