with yoko as (
select survey_year as "SV_YEAR",
       e.pf_code,
       pf_name as "PREFECTURE",
       sum(case when elementary is not null then elementary else 0 end) as elementary,
       sum(case when middle is not null then middle else 0 end) as middle,
       sum(case when high is not null then high else 0 end) as high,
       sum(case when junior_clg is not null then junior_clg else 0 end) as junior_clg,
       sum(case when college is not null then college else 0 end) as college,
       sum(case when graduate is not null then graduate else 0 end) as graduate
from ENROLLMENT_STATUS as e
left join prefecture as p on p.pf_code = e.pf_code
where survey_year = '2020'
group by 1,2,3
),

p as (
select '小学校' as "KIND", 0 as "num"
union all
select '中学校' as "KIND", 1 as "num"
union all
select '高校' as "KIND", 2 as "num"
union all
select '短大' as "KIND", 3 as "num"
union all
select '大学' as "KIND", 4 as "num"
union all
select '大学院' as "KIND", 5 as "num"
)

select sv_year as "SV_YEAR",
       prefecture as "PREFECTURE",
       kind as "KIND",
       case kind when '小学校' then elementary
                 when '中学校' then middle
                 when '高校' then high
                 when '短大' then junior_clg
                 when '大学' then college
                 when '大学院' then graduate end as "AMT"
from yoko
cross join p 
where AMT != 0
order by pf_code,num