select 
	SURVEY_YEAR as SV_YEAR
	,PF_NAME as PREFECTURE
	,KIND
	,AMT
from (
    select 
    	SURVEY_YEAR
    	,t1.PF_CODE
    	,PF_NAME
    	,1 as kind_order
    	,'小学校' as KIND
    	,sum(case when ELEMENTARY >0 then ELEMENTARY else 0 end) as AMT
    from ENROLLMENT_STATUS as t1
    inner join PREFECTURE as t2
    	on t1.PF_CODE = t2.PF_CODE
    where 
    	SURVEY_YEAR = 2020
    group by
        t1.PF_CODE
    
    union all
    select
    	SURVEY_YEAR
    	,t1.PF_CODE
    	,PF_NAME
    	,2 as kind_order
    	,'中学校' as KIND
    	,sum(case when MIDDLE >0 then MIDDLE else 0 end) as AMT
    from ENROLLMENT_STATUS as t1
    inner join PREFECTURE as t2
    	on t1.PF_CODE = t2.PF_CODE
    where 
    	SURVEY_YEAR = 2020
    group by
        t1.PF_CODE
    
    union all
    select
    	SURVEY_YEAR
    	,t1.PF_CODE
    	,PF_NAME
    	,3 as kind_order
    	,'高校' as KIND
    	,sum(case when HIGH >0 then HIGH else 0 end) as AMT
    from ENROLLMENT_STATUS as t1
    inner join PREFECTURE as t2
    	on t1.PF_CODE = t2.PF_CODE
    where 
    	SURVEY_YEAR = 2020
    group by
        t1.PF_CODE
    
    union all
    select
    	SURVEY_YEAR
    	,t1.PF_CODE
    	,PF_NAME
    	,4 as kind_order
    	,'短大' as KIND
    	,sum(case when JUNIOR_CLG >0 then JUNIOR_CLG else 0 end) as AMT
    from ENROLLMENT_STATUS as t1
    inner join PREFECTURE as t2
    	on t1.PF_CODE = t2.PF_CODE
    where 
    	SURVEY_YEAR = 2020
    group by
        t1.PF_CODE
    
    union all
    select
    	SURVEY_YEAR
    	,t1.PF_CODE
    	,PF_NAME
    	,5 as kind_order
    	,'大学' as KIND
    	,sum(case when COLLEGE >0 then COLLEGE else 0 end) as AMT
    from ENROLLMENT_STATUS as t1
    inner join PREFECTURE as t2
    	on t1.PF_CODE = t2.PF_CODE
    where 
    	SURVEY_YEAR = 2020
    group by
        t1.PF_CODE
    
    union all
    select
    	SURVEY_YEAR
    	,t1.PF_CODE
    	,PF_NAME
    	,6 as kind_order
    	,'大学院' as KIND
    	,sum(case when GRADUATE >0 then GRADUATE else 0 end) as AMT
    from ENROLLMENT_STATUS as t1
    inner join PREFECTURE as t2
    	on t1.PF_CODE = t2.PF_CODE
    where 
    	SURVEY_YEAR = 2020
    group by
        t1.PF_CODE
)
where AMT >0
order by
	PF_CODE
	,kind_order