WITH SUMTABLE AS (
    SELECT PF_CODE,
           SURVEY_YEAR,
           SUM(ELEMENTARY) AS SUM_ELE,
           SUM(MIDDLE) AS SUM_MID,
           SUM(HIGH) AS SUM_HIG,
           SUM(JUNIOR_CLG) AS SUM_JUN,
           SUM(COLLEGE) AS SUM_COL,
           SUM(GRADUATE) AS SUM_GRA
    FROM ENROLLMENT_STATUS
    WHERE SURVEY_YEAR = 2020
    GROUP BY PF_CODE
)

SELECT SURVEY_YEAR AS SV_YEAR, PF_NAME AS PREFECTURE, KIND, AMT
FROM (
    SELECT SURVEY_YEAR, PF_CODE, SUM_ELE AS AMT, '小学校' AS KIND, 1 AS CAT
    FROM SUMTABLE
    
    UNION
    SELECT SURVEY_YEAR, PF_CODE, SUM_MID AS AMT, '中学校' AS KIND, 2 AS CAT
    FROM SUMTABLE
    
    UNION
    SELECT SURVEY_YEAR, PF_CODE, SUM_HIG AS AMT, '高校' AS KIND, 3 AS CAT
    FROM SUMTABLE
    
    UNION
    SELECT SURVEY_YEAR, PF_CODE, SUM_JUN AS AMT, '短大' AS KIND, 4 AS CAT
    FROM SUMTABLE
    
    UNION
    SELECT SURVEY_YEAR, PF_CODE, SUM_COL AS AMT, '大学' AS KIND, 5 AS CAT
    FROM SUMTABLE
    
    UNION
    SELECT SURVEY_YEAR, PF_CODE, SUM_GRA AS AMT, '大学院' AS KIND, 6 AS CAT
    FROM SUMTABLE
) AS NUM
LEFT JOIN PREFECTURE ON NUM.PF_CODE = PREFECTURE.PF_CODE
WHERE AMT IS NOT NULL
ORDER BY NUM.PF_CODE ASC, CAT ASC