with syo as (
 select PF_CODE,SURVEY_YEAR,sum(ifnull(ELEMENTARY,0)) as AMT,'小学校' as KIND, 1 as odr
 from ENROLLMENT_STATUS
 where SURVEY_YEAR = 2020 and ELEMENTARY is not null
 group by PF_CODE
)
,chu as (
 select PF_CODE,SURVEY_YEAR,sum(ifnull(MIDDLE,0)) as AMT,'中学校' as KIND, 2 as odr
 from ENROLLMENT_STATUS
 where SURVEY_YEAR = 2020 and MIDDLE is not null
 group by PF_CODE
)
,koko as (
 select PF_CODE,SURVEY_YEAR,sum(ifnull(HIGH,0)) as AMT,'高校' as KIND, 3 as odr
 from ENROLLMENT_STATUS
 where SURVEY_YEAR = 2020 and HIGH is not null
 group by PF_CODE
)
,tan as (
 select PF_CODE,SURVEY_YEAR,sum(ifnull(JUNIOR_CLG,0)) as AMT,'短大' as KIND, 4 as odr
 from ENROLLMENT_STATUS
 where SURVEY_YEAR = 2020 and JUNIOR_CLG is not null
 group by PF_CODE
)
,dai as (
 select PF_CODE,SURVEY_YEAR,sum(ifnull(COLLEGE,0)) as AMT,'大学' as KIND, 5 as odr
 from ENROLLMENT_STATUS
 where SURVEY_YEAR = 2020 and COLLEGE is not null
 group by PF_CODE
)
,daiga as (
 select PF_CODE,SURVEY_YEAR,sum(ifnull(GRADUATE,0)) as AMT,'大学院' as KIND, 6 as odr
 from ENROLLMENT_STATUS
 where SURVEY_YEAR = 2020 and GRADUATE is not null
 group by PF_CODE
)
select 
   kin.SURVEY_YEAR as SV_YEAR
 ,pr.PF_NAME as PREFECTURE
 ,kin.kind as KIND
 ,kin.AMT as AMT
from 
  PREFECTURE pr
,(
  select * from syo union all
  select * from chu union all
  select * from koko union all
  select * from tan union all
  select * from dai union all
  select * from daiga
) kin
where 
pr.PF_CODE = kin.PF_CODE
and (AMT is not null and AMT <> 0)
order by 
  pr.PF_CODE
 ,kin.odr
;