with seq_tbl as (
    select 1 as seq_no
    union all
    select 
        seq_no + 1
    from
        seq_tbl
    where seq_no<6
)
select 
    es_sub.survey_year as SV_YEAR
    , pf.pf_name as PREFECTURE
    , es_sub.kind as KIND
    
from (select 
        es.survey_year
        , es.pf_code
        , st.seq_no
        , case st.seq_no
            when 1 then '小学校'
            when 2 then '中学校'
            when 3 then '高校'
            when 4 then '短大'
            when 5 then '大学'
            when 6 then '大学院'
            else null end as kind
        , case st.seq_no
            when 1 then es.elementary
            when 2 then es.middle
            when 3 then es.high
            when 4 then es.junior_clg
            when 5 then es.college
            when 6 then es.graduate
            else null end as amount
      from ENROLLMENT_STATUS es cross join seq_tbl as st
      where es.survey_year=2020) as es_sub
inner join prefecture pf on es_sub.pf_code=pf.pf_code
where es_sub.amount is not null
group by es_sub.survey_year, pf.pf_name, es_sub.kind
order by es_sub.pf_code asc, es_sub.seq_no asc;