with
	e as (
		select
			pf_code
			,'ELEMENTARY' as kind
			,sum(elementary) as amt
		from
			enrollment_status
		where
			survey_year = 2020
		group by
			1
		union
		select
			pf_code
			,'MIDDLE' as kind
			,sum(middle) as amt
		from
			enrollment_status
		where
			survey_year = 2020
		group by
			1
		union
		select
			pf_code
			,'HIGH' as kind
			,sum(high) as amt
		from
			enrollment_status
		where
			survey_year = 2020
		group by
			1
		union
		select
			pf_code
			,'JUNIOR_CLG' as kind
			,sum(junior_clg) as amt
		from
			enrollment_status
		where
			survey_year = 2020
		group by
			1
		union
		select
			pf_code
			,'COLLEGE' as kind
			,sum(college) as amt
		from
			enrollment_status
		where
			survey_year = 2020
		group by
			1
		union
		select
			pf_code
			,'GRADUATE' as kind
			,sum(graduate) as amt
		from
			enrollment_status
		where
			survey_year = 2020
		group by
			1
	),
	k as (
		select
			'ELEMENTARY' as kind
		union
		select
			'MIDDLE' as kind
		union
		select
			'HIGH' as kind
		union
		select
			'JUNIOR_CLG' as kind
		union
		select
			'COLLEGE' as kind
		union
		select
			'GRADUATE' as kind
	),
	pk as (
		select
			p.pf_code
			,p.pf_name
			,k.kind
		from
			prefecture p
			cross join k
	)

select
	2020 as SV_YEAR
	,pk.pf_name as PREFECTURE
	,case
		pk.kind
			when 'ELEMENTARY' then '小学校'
			when 'MIDDLE' then '中学校'
			when 'HIGH' then '高校'
			when 'JUNIOR_CLG' then '短大'
			when 'COLLEGE' then '大学'
			when 'GRADUATE' then '大学院'
			end	as KIND
	,e.amt as AMT
from
	pk
	join
		e
		on
			pk.pf_code = e.pf_code
			and pk.kind = e.kind
where
	amt is not null
order by
	pk.pf_code asc
	,case
		pk.kind
			when 'ELEMENTARY' then 1
			when 'MIDDLE' then 2
			when 'HIGH' then 3
			when 'JUNIOR_CLG' then 4
			when 'COLLEGE' then 5
			when 'GRADUATE' then 6
			end