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
  ,'小学校' as KIND
  ,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
  ,'中学校' as KIND
  ,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
  ,'高校' as KIND
  ,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
  ,'短大' as KIND
  ,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
  ,'大学' as KIND
  ,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
  ,'大学院' as KIND
  ,GRADUATE as AMT
from base_tbl a
inner join PREFECTURE b
on a.PF_CODE=b.PF_CODE
)
select
  SV_YEAR
  ,PREFECTURE
  ,KIND
  ,AMT
from base_tbl2
order by CODE