with SCHOOL as (
    select 1 as num
    union all
    select num + 1 from SCHOOL where num < 6
),

TATE as (
select
    E.SURVEY_YEAR as SV_YEAR,
    E.PF_CODE as PF_CODE,
    max(case when S.num = 1 then '小学校' when S.num = 2 then '中学校' when S.num = 3 then '高校' when S.num = 4 then '短大' when S.num = 5 then '大学' when S.num = 6 then '大学院' else 'X' end) 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
cross join
    SCHOOL S
group by
    SV_YEAR,
    PF_CODE,
    ORD
)

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