WITH tbl_1 AS (
    SELECT
        SURVEY_YEAR AS SV_YEAR
        ,PF_NAME AS PREFECTURE
        ,tbl_e.PF_CODE AS PF_CODE
        ,SUM(ELEMENTARY) AS 小学校
        ,SUM(MIDDLE) AS 中学校
        ,SUM(HIGH) AS 高校
        ,SUM(JUNIOR_CLG) AS 短大
        ,SUM(COLLEGE) AS 大学
        ,SUM(GRADUATE) AS 大学院
    FROM 
        ENROLLMENT_STATUS AS tbl_e
    INNER JOIN 
	    PREFECTURE AS tbl_p
    ON 
	    tbl_e.PF_CODE = tbl_p.PF_CODE
    WHERE 
        SURVEY_YEAR = 2020
    GROUP BY
        PF_NAME
), tmp_db2 AS ( 
    SELECT 
        SV_YEAR
        ,PREFECTURE
        ,PF_CODE
        ,'小学校' AS KIND
        ,小学校 AS AMT
        ,1 AS 'number'
    FROM 
    	tbl_1
    	
    UNION ALL
    SELECT 
        SV_YEAR
        ,PREFECTURE
        ,PF_CODE
        ,'中学校' AS KIND
        ,中学校 AS AMT
        ,2 AS 'number'
    FROM 
    	tbl_1
    	
    UNION ALL
    SELECT 
        SV_YEAR
        ,PREFECTURE
        ,PF_CODE
        ,'高校' AS KIND
        ,高校 AS AMT
        ,3 AS 'number'
    FROM 
    	tbl_1
    	
    UNION ALL
    SELECT 
        SV_YEAR
        ,PREFECTURE
        ,PF_CODE
        ,'短大' AS KIND
        ,短大 AS AMT
        ,4 AS 'number'
    FROM 
    	tbl_1
    	
    UNION ALL
    SELECT 
        SV_YEAR
        ,PREFECTURE
        ,PF_CODE
        ,'大学' AS KIND
        ,大学 AS AMT
        ,5 AS 'number'
    FROM 
    	tbl_1
    	
    UNION ALL
    SELECT 
        SV_YEAR
        ,PREFECTURE
        ,PF_CODE
        ,'大学院' AS KIND
        ,大学院 AS AMT
        ,6 AS 'number'
    FROM 
    	tbl_1
)
SELECT 
    SV_YEAR
    ,PREFECTURE
    ,KIND
    ,AMT
FROM
    tmp_db2
WHERE 
    AMT IS NOT NULL
ORDER BY
    PF_CODE
    ,'number';