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 survey_year = 2020 and ELEMENTARY is not null
    union 
    select survey_year , pf_code , age, 2 , '中学校' , MIDDLE from ENROLLMENT_STATUS
        where survey_year = 2020 and MIDDLE is not null
    union 
    select survey_year , pf_code , age, 3 , '高校' , HIGH from ENROLLMENT_STATUS
        where survey_year = 2020 and HIGH is not null
    union
    select survey_year , pf_code , age, 4 , '短大' , JUNIOR_CLG from ENROLLMENT_STATUS
        where survey_year = 2020 and JUNIOR_CLG is not null
    union
    select survey_year , pf_code , age, 5 , '大学' , COLLEGE from ENROLLMENT_STATUS
        where survey_year = 2020 and COLLEGE is not null
    union
    select survey_year , pf_code , age, 6 , '大学院' , GRADUATE from ENROLLMENT_STATUS
        where survey_year = 2020 and GRADUATE is not null    
) as A
inner join PREFECTURE as B on  A.pf_code = B.pf_code
group by survey_year , A.pf_code , seq
order by A.pf_code , seq