with school as (
select ENROLLMENT_STATUS.SURVEY_YEAR
	,ENROLLMENT_STATUS.PF_CODE 
	,PREFECTURE.PF_NAME
	,sum(ELEMENTARY) as sum_elementary
	,sum(MIDDLE) as sum_middle
	,sum(HIGH) as sum_high
	,sum(JUNIOR_CLG) as sum_junior_clg
	,sum(college) as sum_college
	,sum(graduate) as sum_graduate 
from ENROLLMENT_STATUS 
inner join PREFECTURE on ENROLLMENT_STATUS.PF_CODE = PREFECTURE.PF_CODE
where ENROLLMENT_STATUS.SURVEY_YEAR = 2020
group by ENROLLMENT_STATUS.SURVEY_YEAR,ENROLLMENT_STATUS.PF_CODE
)
, hoge as (
select SURVEY_YEAR as SV_YEAR
	,PF_CODE 
	,PF_NAME as PREFECTURE
	,'小学校' as KIND
	,sum_elementary as AMT
from school
where AMT is not null
union all 
select SURVEY_YEAR as SV_YEAR
,PF_CODE 
	,PF_NAME as PREFECTURE
	,'中学校' as KIND
	,sum_middle as AMT
from school
where AMT is not null
union all 
select SURVEY_YEAR as SV_YEAR
,PF_CODE 
	,PF_NAME as PREFECTURE
	,'高校' as KIND
	,sum_high as AMT
from school
where AMT is not null
union all 
select SURVEY_YEAR as SV_YEAR
,PF_CODE 
	,PF_NAME as PREFECTURE
	,'短大' as KIND
	,sum_junior_clg as AMT
from school
where AMT is not null
union all 
select SURVEY_YEAR as SV_YEAR
,PF_CODE 
	,PF_NAME as PREFECTURE
	,'大学' as KIND
	,sum_college as AMT
from school
where AMT is not null
union all 
select SURVEY_YEAR as SV_YEAR
,PF_CODE 
	,PF_NAME as PREFECTURE
	,'大学院' as KIND
	,sum_graduate as AMT
from school
where AMT is not null
)
select SV_YEAR,PREFECTURE,KIND,AMT from hoge order by PF_CODE