with base_tbl as
(
select
  SURVEY_YEAR
  ,PF_CODE
  ,sum(ELEMENTARY) as ELEMENTARY
  ,sum(MIDDLE) as MIDDLE
  ,sum(HIGH) as HIGH
  ,sum(JUNIOR_CLG) as JUNIOR_CLG
  ,sum(COLLEGE) as COLLEGE
  ,sum(GRADUATE) as GRADUATE
from ENROLLMENT_STATUS
where SURVEY_YEAR=2020
group by 1,2
),
base_tbl2 as
(
select
  SURVEY_YEAR as SV_YEAR
  ,a.PF_CODE as CODE
  ,b.PF_NAME as PREFECTURE
  ,1 as KIND_tmp
  ,ELEMENTARY as AMT
from base_tbl a
inner join PREFECTURE b
on a.PF_CODE=b.PF_CODE
union all
select
  SURVEY_YEAR as SV_YEAR
  ,a.PF_CODE as CODE
  ,b.PF_NAME as PREFECTURE
  ,2 as KIND_tmp
  ,MIDDLE as AMT
from base_tbl a
inner join PREFECTURE b
on a.PF_CODE=b.PF_CODE
union all
select
  SURVEY_YEAR as SV_YEAR
  ,a.PF_CODE as CODE
  ,b.PF_NAME as PREFECTURE
  ,3 as KIND_tmp
  ,HIGH as AMT
from base_tbl a
inner join PREFECTURE b
on a.PF_CODE=b.PF_CODE
union all
select
  SURVEY_YEAR as SV_YEAR
  ,a.PF_CODE as CODE
  ,b.PF_NAME as PREFECTURE
  ,4 as KIND_tmp
  ,JUNIOR_CLG as AMT
from base_tbl a
inner join PREFECTURE b
on a.PF_CODE=b.PF_CODE
union all
select
  SURVEY_YEAR as SV_YEAR
  ,a.PF_CODE as CODE
  ,b.PF_NAME as PREFECTURE
  ,5 as KIND_tmp
  ,COLLEGE as AMT
from base_tbl a
inner join PREFECTURE b
on a.PF_CODE=b.PF_CODE
union all
select
  SURVEY_YEAR as SV_YEAR
  ,a.PF_CODE as CODE
  ,b.PF_NAME as PREFECTURE
  ,6 as KIND_tmp
  ,GRADUATE as AMT
from base_tbl a
inner join PREFECTURE b
on a.PF_CODE=b.PF_CODE
)
select
  SV_YEAR
  ,PREFECTURE
  ,case
    when KIND_tmp=1 then "小学校"
    when KIND_tmp=2 then "中学校"
    when KIND_tmp=3 then "高校"
    when KIND_tmp=4 then "短大"
    when KIND_tmp=5 then "大学"
    when KIND_tmp=6 then "大学院"
  end as KIND
  ,AMT
from base_tbl2
where AMT is not null
order by CODE