select A.survey_year as SV_YEAR , B.pf_name as PREFECTURE , A. kind as KIND , sum (A.amt) as AMT
from (
    select 
        survey_year  , pf_code ,age , 1 as seq , '小学校' as KIND , ELEMENTARY as AMT from ENROLLMENT_STATUS 
        where ifnull(ELEMENTARY ,0) != 0
    union 
    select survey_year , pf_code , age, 2 , '中学校' , MIDDLE from ENROLLMENT_STATUS
        where ifnull(MIDDLE ,0) != 0
    union 
    select survey_year , pf_code , age, 3 , '高校' , HIGH from ENROLLMENT_STATUS
        where ifnull(HIGH ,0) != 0
    union
    select survey_year , pf_code , age, 4 , '短大' , JUNIOR_CLG from ENROLLMENT_STATUS
        where ifnull(JUNIOR_CLG ,0) != 0
    union
    select survey_year , pf_code , age, 5 , '大学' , COLLEGE from ENROLLMENT_STATUS
        where ifnull(COLLEGE ,0) != 0
    union
    select survey_year , pf_code , age, 6 , '大学院' , GRADUATE from ENROLLMENT_STATUS
        where ifnull(GRADUATE ,0) != 0    
) as A
inner join PREFECTURE as B on  A.pf_code = B.pf_code
where survey_year = 2020 and AMT is not null
group by survey_year , A.pf_code , seq
order by A.pf_code , seq