with a as (
    select pf_code,'1小学校'  as k, sum(ELEMENTARY) v from ENROLLMENT_STATUS where SURVEY_YEAR=2020 and ELEMENTARY is not null group by  pf_code
    union all
    select pf_code,'2中学校'  as k, sum(MIDDLE) v from ENROLLMENT_STATUS where SURVEY_YEAR=2020 and ELEMENTARY is not null group by  pf_code
    union all
    select pf_code,'3高校'  as k, sum(HIGH) v from ENROLLMENT_STATUS where SURVEY_YEAR=2020 and HIGH is not null group by  pf_code
    union all
    select pf_code,'4短大'  as k, sum(JUNIOR_CLG) v from ENROLLMENT_STATUS where SURVEY_YEAR=2020 and JUNIOR_CLG is not null group by  pf_code
    union all
    select pf_code,'5大学'  as k, sum(COLLEGE) v from ENROLLMENT_STATUS where SURVEY_YEAR=2020 and COLLEGE is not null  group by  pf_code
    union all
    select pf_code,'6大学院'  as k, sum(GRADUATE) v from ENROLLMENT_STATUS where SURVEY_YEAR=2020 and GRADUATE is not null  group by  pf_code
)
select 
2020 as SV_YEAR,
PF_NAME  as PREFECTURE,
substr(k,2) as KIND,
v AMT
from a join PREFECTURE on a.pf_code = prefecture.pf_code where v is not null
order by a.pf_code,k