with temp as(
    select SURVEY_YEAR, PF_CODE, 1 as kind_code,  '小学校' as KIND, sum(ELEMENTARY) as amt from ENROLLMENT_STATUS where SURVEY_YEAR = 2020 group by PF_CODE
    union all 
    select SURVEY_YEAR, PF_CODE, 2 as kind_code, '中学校' as KIND, sum(MIDDLE) as amt from ENROLLMENT_STATUS where SURVEY_YEAR = 2020 group by PF_CODE
    union all 
    select SURVEY_YEAR, PF_CODE, 3 as kind_code, '高校' as KIND, sum(HIGH) as amt from ENROLLMENT_STATUS where SURVEY_YEAR = 2020 group by PF_CODE
    union all 
    select SURVEY_YEAR, PF_CODE, 4 as kind_code, '短大' as KIND, sum(JUNIOR_CLG) as amt from ENROLLMENT_STATUS where SURVEY_YEAR = 2020 group by PF_CODE
    union all 
    select SURVEY_YEAR, PF_CODE, 5 as kind_code, '大学' as KIND, sum(COLLEGE) as amt from ENROLLMENT_STATUS where SURVEY_YEAR = 2020 group by PF_CODE
    union all 
    select SURVEY_YEAR, PF_CODE, 6 as kind_code, '大学院' as KIND, sum(GRADUATE) as amt from ENROLLMENT_STATUS where SURVEY_YEAR = 2020 group by PF_CODE
)
select 
    SURVEY_YEAR as SV_YEAR, 
    p.PF_NAME as PREFECTURE, 
    KIND, 
    amt as AMT
from temp as t
    inner join PREFECTURE as p on t.PF_CODE = p.PF_CODE
where amt is not null
order by p.PF_CODE asc, kind_code asc
;