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 is null then 0 else ELEMENTARY 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 is null then 0 else MIDDLE 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 is null then 0 else HIGH 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 is null then 0 else JUNIOR_CLG 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 is null then 0 else COLLEGE 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 is null then 0 else GRADUATE 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