select A.sv_year as SV_YEAR , B.pf_name as PREFECTURE , A. kind as KIND , sum (A.amt) as AMT

from (

    select 
        survey_year as sv_year , pf_code ,age , 1 as seq , '小学校' as KIND , ELEMENTARY as AMT from ENROLLMENT_STATUS 
        where survey_year = 2020 and ifnull(ELEMENTARY ,0) != 0
    union 
    select survey_year , pf_code , age, 2 , '中学校' , MIDDLE from ENROLLMENT_STATUS
        where survey_year = 2020 and ifnull(MIDDLE ,0) != 0
    union 
    select survey_year , pf_code , age, 3 , '高校' , HIGH from ENROLLMENT_STATUS
     where survey_year = 2020 and ifnull(HIGH ,0) != 0
    union
    select survey_year , pf_code , age, 4 , '短大' , JUNIOR_CLG from ENROLLMENT_STATUS
        where survey_year = 2020 and ifnull(JUNIOR_CLG ,0) != 0
    union
    select survey_year , pf_code , age, 5 , '大学' , COLLEGE from ENROLLMENT_STATUS
        where survey_year = 2020 and ifnull(COLLEGE ,0) != 0
    union
    select survey_year , pf_code , age, 6 , '大学院' , GRADUATE from ENROLLMENT_STATUS
        where survey_year = 2020 and ifnull(GRADUATE ,0) != 0    
) as A

inner join PREFECTURE as B on  A.pf_code = B.pf_code

group by sv_year , A.pf_code , seq
order by A.pf_code , seq