WITH 
tbl AS (
    SELECT 
        SURVEY_YEAR AS SV_YEAR,
        PREFECTURE.PF_CODE,
        PREFECTURE.PF_NAME AS PREFECTURE,
        SUM(ELEMENTARY) AS AMT_ELM,
        SUM(MIDDLE) AS AMT_MID,
        SUM(HIGH) AS AMT_HIGH,
        SUM(JUNIOR_CLG) AS AMT_JCLG,
        SUM(COLLEGE) AS AMT_CLG,
        SUM(GRADUATE) AS AMT_GRD
    FROM 
        ENROLLMENT_STATUS
    INNER JOIN 
        PREFECTURE
    ON
        ENROLLMENT_STATUS.PF_CODE = PREFECTURE.PF_CODE
    WHERE 
        SURVEY_YEAR = 2020
    GROUP BY 
        SURVEY_YEAR,
        PREFECTURE.PF_CODE,
        PREFECTURE.PF_NAME
)

SELECT 
    SV_YEAR,
    PREFECTURE,
    KIND,
    AMT
FROM (
SELECT 
    SV_YEAR,
    PF_CODE,
    PREFECTURE,
    "小学校" AS KIND,
    1 AS KIND_IDX,
    AMT_ELM AS AMT
FROM 
    tbl
    
UNION ALL 

SELECT 
    SV_YEAR,
    PF_CODE,
    PREFECTURE,
    "中学校" AS KIND,
    2 AS KIND_IDX,
    AMT_MID AS AMT
FROM 
    tbl
    
UNION ALL 

SELECT 
    SV_YEAR,
    PF_CODE,
    PREFECTURE,
    "高校" AS KIND,
    3 AS KIND_IDX,
    AMT_HIGH AS AMT
FROM 
    tbl
    
UNION ALL 

SELECT 
    SV_YEAR,
    PF_CODE,
    PREFECTURE,
    "短大" AS KIND,
    4 AS KIND_IDX,
    AMT_JCLG AS AMT
FROM 
    tbl
    
UNION ALL 

SELECT 
    SV_YEAR,
    PF_CODE,
    PREFECTURE,
    "大学" AS KIND,
    5 AS KIND_IDX,
    AMT_CLG AS AMT
FROM 
    tbl
    
UNION ALL 

SELECT 
    SV_YEAR,
    PF_CODE,
    PREFECTURE,
    "大学院" AS KIND,
    6 AS KIND_IDX,
    AMT_GRD AS AMT
FROM 
    tbl
)
WHERE 
    AMT IS NOT NULL
ORDER BY
    PREFECTURE,
    KIND_IDX