with elem as (
select PF_CODE, "小学校" as KIND, sum(ELEMENTARY) as total, 1 as ord from ENROLLMENT_STATUS where SURVEY_YEAR = 2020
group by PF_CODE having sum(ELEMENTARY) is not null
),
middle as(
select PF_CODE,  "中学校" as KIND, sum(MIDDLE) as total, 2 as ord from ENROLLMENT_STATUS where SURVEY_YEAR = 2020
group by PF_CODE having sum(MIDDLE) is not null
),
high as (
select PF_CODE,  "高校" as KIND, sum(HIGH) as total, 3 as ord from ENROLLMENT_STATUS where SURVEY_YEAR = 2020
group by PF_CODE having sum(HIGH) is not null
),
low_high as (
select PF_CODE,  "短大" as KIND, sum(JUNIOR_CLG) as total, 4 as ord from ENROLLMENT_STATUS where SURVEY_YEAR = 2020
group by PF_CODE having sum(JUNIOR_CLG) is not null
),
coll as (
select PF_CODE,  "大学" as KIND, sum(COLLEGE) as total, 5 as ord from ENROLLMENT_STATUS where SURVEY_YEAR = 2020
group by PF_CODE having sum(COLLEGE) is not null
),
grad as (
select PF_CODE,  "大学院" as KIND, sum(GRADUATE) as total, 6 as ord from ENROLLMENT_STATUS where SURVEY_YEAR = 2020
group by PF_CODE having sum(GRADUATE) is not null
),
summary as (
select
PREFECTURE.PF_CODE,
2020 as SV_YEAR,
PREFECTURE.PF_NAME as PREFECTURE,
"小学校" as KIND,
elem.total as AMT,
ord
from
PREFECTURE
join elem on elem.PF_CODE = PREFECTURE.PF_CODE
UNION
select
PREFECTURE.PF_CODE,
2020 as SV_YEAR,
PREFECTURE.PF_NAME as PREFECTURE,
"中学校" as KIND,
middle.total as AMT,
ord
from
PREFECTURE
join middle on middle.PF_CODE = PREFECTURE.PF_CODE
UNION
select
PREFECTURE.PF_CODE,
2020 as SV_YEAR,
PREFECTURE.PF_NAME as PREFECTURE,
"高校" as KIND,
high.total as AMT,
ord
from
PREFECTURE
join high on high.PF_CODE = PREFECTURE.PF_CODE
UNION
select
PREFECTURE.PF_CODE,
2020 as SV_YEAR,
PREFECTURE.PF_NAME as PREFECTURE,
"短大" as KIND,
low_high.total as AMT,
ord
from
PREFECTURE
join low_high on low_high.PF_CODE = PREFECTURE.PF_CODE
UNION
select
PREFECTURE.PF_CODE,
2020 as SV_YEAR,
PREFECTURE.PF_NAME as PREFECTURE,
"大学" as KIND,
coll.total as AMT,
ord
from
PREFECTURE
join coll on coll.PF_CODE = PREFECTURE.PF_CODE
UNION
select
PREFECTURE.PF_CODE,
2020 as SV_YEAR,
PREFECTURE.PF_NAME as PREFECTURE,
"大学院" as KIND,
grad.total as AMT,
ord
from
PREFECTURE
join grad on grad.PF_CODE = PREFECTURE.PF_CODE
)
select SV_YEAR, PREFECTURE, KIND, AMT from summary
order by PF_CODE, ord