with vTbl as
(select
     es.SURVEY_YEAR 
    ,PF_CODE
    ,'小学生' KIND
    ,1 as rnk 
    ,sum(ELEMENTARY) AMT
    from ENROLLMENT_STATUS es
    where ELEMENTARY>0
    and es.SURVEY_YEAR=2020
union all
    select
     es.SURVEY_YEAR 
    ,PF_CODE
    ,'中学生' KIND
    ,2 as rnk 
    ,sum(MIDDLE) AMT
    from ENROLLMENT_STATUS es
    where MIDDLE>0
    and es.SURVEY_YEAR=2020
union all
    select
     es.SURVEY_YEAR 
    ,PF_CODE
    ,'高校生' KIND
    ,3 as rnk 
    ,sum(HIGH) AMT
    from ENROLLMENT_STATUS es
    where HIGH>0
    and es.SURVEY_YEAR=2020
union all
    select
     es.SURVEY_YEAR 
    ,PF_CODE
    ,'短大' KIND
    ,4 as rnk 
    ,sum(JUNIOR_CLG) AMT
    from ENROLLMENT_STATUS es
    where JUNIOR_CLG>0
    and es.SURVEY_YEAR=2020
union all
    select
     es.SURVEY_YEAR 
    ,PF_CODE
    ,'大学' KIND
    ,5 as rnk 
    ,sum(COLLEGE) AMT
    from ENROLLMENT_STATUS es
    where COLLEGE>0
    and es.SURVEY_YEAR=2020
union all
    select
     es.SURVEY_YEAR 
    ,PF_CODE
    ,'大学院' KIND
    ,6 as rnk 
    ,sum(GRADUATE) AMT
    from ENROLLMENT_STATUS es
    where GRADUATE>0
    and es.SURVEY_YEAR=2020
)
select 
 t.SURVEY_YEAR as SV_YEAR
,p.PF_NAME as PREFECTURE
,t.KIND as KIND
,t.AMT as AMT
from vTbl t
inner join PREFECTURE p
    on t.PF_CODE = p.PF_CODE
order by 
  t.PF_CODE asc
 ,t.rnk