select
     main.SURVEY_YEAR as SV_YEAR
    ,pre.PF_NAME as PREFECTURE
    ,case when p.pattern = 1 then '小学校'
          when p.pattern = 2 then '中学校'
          when p.pattern = 3 then '高校'
          when p.pattern = 4 then '短大'
          when p.pattern = 5 then '大学'
          when p.pattern = 6 then '大学院'
          else null
     end as KIND
    ,sum(case when p.pattern = 1 then ELEMENTARY
          when p.pattern = 2 then MIDDLE
          when p.pattern = 3 then HIGH
          when p.pattern = 4 then JUNIOR_CLG
          when p.pattern = 5 then COLLEGE
          when p.pattern = 6 then GRADUATE
          else null
     end) as AMT
from
    ENROLLMENT_STATUS as main
inner join
    PREFECTURE as pre using(PF_CODE)
cross join
(
   SELECT 1 AS pattern
    UNION ALL SELECT 2 AS pattern
    UNION ALL SELECT 3 AS pattern
    UNION ALL SELECT 4 AS pattern
    UNION ALL SELECT 5 AS pattern
    UNION ALL SELECT 6 AS pattern
) as p
where
    main.SURVEY_YEAR = 2020
group by
     SV_YEAR
    ,PREFECTURE
    ,KIND
having
    AMT is not null
order by
     PREFECTURE asc
    ,p.pattern asc