SELECT
EN.SURVEY_YEAR as SV_YEAR,
PR.PF_NAME AS PREFECTURE,
EN1.KIND,
EN1.AMT
FROM ENROLLMENT_STATUS AS EN
INNER JOIN PREFECTURE AS PR 
ON EN.PF_CODE = PR.PF_CODE
LEFT OUTER JOIN 
(
select '小学校' as KIND, '1' as ss, SUM(ELEMENTARY) as AMT,PF_CODE from ENROLLMENT_STATUS where SURVEY_YEAR ='2020' and ELEMENTARY IS NOT NULL  group by PF_CODE
union all
select '中学校' as KIND, '2' as ss, SUM(MIDDLE) as AMT,PF_CODE from ENROLLMENT_STATUS where SURVEY_YEAR ='2020' and MIDDLE IS NOT NULL group by PF_CODE
union all
select '高校' as KIND, '3' as ss, SUM(HIGH) as AMT,PF_CODE from ENROLLMENT_STATUS where SURVEY_YEAR ='2020' and HIGH IS NOT NULL group by PF_CODE
union all
select '短大' as KIND, '4' as ss, SUM(JUNIOR_CLG) as AMT,PF_CODE from ENROLLMENT_STATUS where SURVEY_YEAR ='2020' and JUNIOR_CLG IS NOT NULL group by PF_CODE
union all
select '大学' as KIND, '5' as ss, SUM(COLLEGE) as AMT,PF_CODE from ENROLLMENT_STATUS where SURVEY_YEAR ='2020' and COLLEGE IS NOT NULL group by PF_CODE
union all
select '大学院' as KIND, '6' as ss, SUM(GRADUATE) as AMT,PF_CODE from ENROLLMENT_STATUS where SURVEY_YEAR ='2020' and GRADUATE IS NOT NULL group by PF_CODE
) EN1  ON EN.PF_CODE = EN1.PF_CODE
where EN.SURVEY_YEAR = '2020'
group by EN.SURVEY_YEAR,PR.PF_NAME,EN1.KIND,EN1.AMT
ORDER BY EN.PF_CODE ASC,EN1.ss ASC;