select SV_YEAR,
PREFECTURE,
KIND,
AMT
from
(select 2020 as "SV_YEAR",
t1.PREFECTURE as "PREFECTURE",
"小学校" as "KIND",
t1.e as "AMT","CODE"
from
(select
p1.PF_NAME as "PREFECTURE",
p1.PF_CODE as "CODE",
SUM(ELEMENTARY) as e,
SUM(MIDDLE) as m,
SUM(HIGH) as h,
SUM(JUNIOR_CLG) as j,
SUM(COLLEGE) as c,
SUM(GRADUATE) as g
from ENROLLMENT_STATUS as e1
inner join PREFECTURE as p1
on e1.PF_CODE =p1.PF_CODE
where SURVEY_YEAR="2020"
group by e1.PF_CODE) as t1
union all
select 2020 as "SV_YEAR",
t1.PREFECTURE as "PREFECTURE",
"中学校" as "KIND",
t1.m as "AMT","CODE"
from
(select
p1.PF_NAME as "PREFECTURE",
p1.PF_CODE as "CODE",
SUM(ELEMENTARY) as e,
SUM(MIDDLE) as m,
SUM(HIGH) as h,
SUM(JUNIOR_CLG) as j,
SUM(COLLEGE) as c,
SUM(GRADUATE) as g
from ENROLLMENT_STATUS as e1
inner join PREFECTURE as p1
on e1.PF_CODE =p1.PF_CODE
where SURVEY_YEAR="2020"
group by e1.PF_CODE) as t1
union all
select 2020 as "SV_YEAR",
t1.PREFECTURE as "PREFECTURE",
"高校" as "KIND",
t1.h as "AMT","CODE"
from
(select
p1.PF_NAME as "PREFECTURE",
p1.PF_CODE as "CODE",
SUM(ELEMENTARY) as e,
SUM(MIDDLE) as m,
SUM(HIGH) as h,
SUM(JUNIOR_CLG) as j,
SUM(COLLEGE) as c,
SUM(GRADUATE) as g
from ENROLLMENT_STATUS as e1
inner join PREFECTURE as p1
on e1.PF_CODE =p1.PF_CODE
where SURVEY_YEAR="2020"
group by e1.PF_CODE) as t1
union all
select 2020 as "SV_YEAR",
t1.PREFECTURE as "PREFECTURE",
"短大" as "KIND",
t1.j as "AMT","CODE"
from
(select
p1.PF_NAME as "PREFECTURE",
p1.PF_CODE as "CODE",
SUM(ELEMENTARY) as e,
SUM(MIDDLE) as m,
SUM(HIGH) as h,
SUM(JUNIOR_CLG) as j,
SUM(COLLEGE) as c,
SUM(GRADUATE) as g
from ENROLLMENT_STATUS as e1
inner join PREFECTURE as p1
on e1.PF_CODE =p1.PF_CODE
where SURVEY_YEAR="2020"
group by e1.PF_CODE) as t1
union all
select 2020 as "SV_YEAR",
t1.PREFECTURE as "PREFECTURE",
"大学" as "KIND",
t1.c as "AMT","CODE"
from
(select
p1.PF_NAME as "PREFECTURE",
p1.PF_CODE as "CODE",
SUM(ELEMENTARY) as e,
SUM(MIDDLE) as m,
SUM(HIGH) as h,
SUM(JUNIOR_CLG) as j,
SUM(COLLEGE) as c,
SUM(GRADUATE) as g
from ENROLLMENT_STATUS as e1
inner join PREFECTURE as p1
on e1.PF_CODE =p1.PF_CODE
where SURVEY_YEAR="2020"
group by e1.PF_CODE) as t1
union all
select 2020 as "SV_YEAR",
t1.PREFECTURE as "PREFECTURE",
"大学院" as "KIND",
t1.g as "AMT","CODE"
from
(select
p1.PF_NAME as "PREFECTURE",
p1.PF_CODE as "CODE",
SUM(ELEMENTARY) as e,
SUM(MIDDLE) as m,
SUM(HIGH) as h,
SUM(JUNIOR_CLG) as j,
SUM(COLLEGE) as c,
SUM(GRADUATE) as g
from ENROLLMENT_STATUS as e1
inner join PREFECTURE as p1
on e1.PF_CODE =p1.PF_CODE
where SURVEY_YEAR="2020"
group by e1.PF_CODE) as t1) as t1
where AMT is not NULL
order by CODE
;