with SCHOOL as (
    select 1 as num,'小学校' as typ union all
    select 2 as num,'中学校' as typ union all
    select 3 as num,'高校' as typ union all
    select 4 as num,'短大' as typ union all
    select 5 as num,'大学' as typ union all
    select 6 as num,'大学院' as typ
),

TATE as (
select
    E.SURVEY_YEAR as SV_YEAR,
    E.PF_CODE as PF_CODE,
    S.typ as KIND,
    sum(case when S.num = 1 then E.ELEMENTARY when S.num = 2 then E.MIDDLE when S.num = 3 then E.HIGH when S.num = 4 then E.JUNIOR_CLG when S.num = 5 then E.COLLEGE when S.num = 6 then E.GRADUATE else -1 end) as AMT,
    S.num as ord
from
    ENROLLMENT_STATUS E
inner join
    SCHOOL S
on
    1 == 1
group by
    SV_YEAR,
    PF_CODE,
    KIND
)

select 
    T.SV_YEAR,
    P.PF_NAME as PREFECTURE,
    T.KIND,
    T.AMT
from
    TATE T
inner join
    PREFECTURE P
on
    T.PF_CODE = P.PF_CODE
    and
    T.AMT is not null
    and
    T.SV_YEAR = 2020
order by
    T.PF_CODE asc,
    T.ord asc