with SCHOOL(num,typ) as (
values 
(1,'小学校'),
(2,'中学校'),
(3,'高校'),
(4,'短大'),
(5,'大学'),
(6,'大学院')
),
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