SELECT K.SV_YEAR AS SV_YEAR
,K.PREFECTURE AS PREFECTURE
,K.KIND AS KIND
,K.AMT AS AMT

FROM (SELECT SURVEY_YEAR AS SV_YEAR
,ES.PF_CODE AS PF_CODE 
,PF_NAME AS PREFECTURE
,'小学校' AS KIND
,1 AS KINDNUMBER
,SUM(ELEMENTARY) AS AMT
FROM ENROLLMENT_STATUS AS ES
LEFT JOIN PREFECTURE AS PR
ON ES.PF_CODE  = PR.PF_CODE 
--INNER JOIN
--ON
WHERE SURVEY_YEAR='2020'
AND ELEMENTARY IS NOT NULL
GROUP BY SURVEY_YEAR 
,PF_NAME
--ORDER BY

union all

SELECT SURVEY_YEAR AS SV_YEAR
,ES.PF_CODE AS PF_CODE 
,PF_NAME AS PREFECTURE
,'中学校' AS KIND
,2 AS KINDNUMBER
,SUM(MIDDLE) AS AMT
FROM ENROLLMENT_STATUS AS ES
LEFT JOIN PREFECTURE AS PR
ON ES.PF_CODE  = PR.PF_CODE 
--INNER JOIN
--ON
WHERE SURVEY_YEAR='2020'
AND MIDDLE IS NOT NULL
GROUP BY SURVEY_YEAR 
,PF_NAME
--ORDER BY

union all

SELECT SURVEY_YEAR AS SV_YEAR
,ES.PF_CODE AS PF_CODE 
,PF_NAME AS PREFECTURE
,'高校' AS KIND
,3 AS KINDNUMBER
,SUM(HIGH) AS AMT
FROM ENROLLMENT_STATUS AS ES
LEFT JOIN PREFECTURE AS PR
ON ES.PF_CODE  = PR.PF_CODE 
WHERE SURVEY_YEAR='2020'
AND HIGH IS NOT NULL
GROUP BY SURVEY_YEAR 
,PF_NAME

union all

SELECT SURVEY_YEAR AS SV_YEAR
,ES.PF_CODE AS PF_CODE 
,PF_NAME AS PREFECTURE
,'短大' AS KIND
,4 AS KINDNUMBER
,SUM(JUNIOR_CLG) AS AMT
FROM ENROLLMENT_STATUS AS ES
LEFT JOIN PREFECTURE AS PR
ON ES.PF_CODE  = PR.PF_CODE 
--INNER JOIN
--ON
WHERE SURVEY_YEAR='2020'
AND JUNIOR_CLG IS NOT NULL
GROUP BY SURVEY_YEAR 
,PF_NAME
--ORDER BY

union all

SELECT SURVEY_YEAR AS SV_YEAR
,ES.PF_CODE AS PF_CODE 
,PF_NAME AS PREFECTURE
,'大学' AS KIND
,5 AS KINDNUMBER
,SUM(COLLEGE) AS AMT
FROM ENROLLMENT_STATUS AS ES
LEFT JOIN PREFECTURE AS PR
ON ES.PF_CODE  = PR.PF_CODE 
--INNER JOIN
--ON
WHERE SURVEY_YEAR='2020'
AND COLLEGE IS NOT NULL
GROUP BY SURVEY_YEAR 
,PF_NAME
--ORDER BY

union all

SELECT SURVEY_YEAR AS SV_YEAR
,ES.PF_CODE AS PF_CODE 
,PF_NAME AS PREFECTURE
,'大学院' AS KIND
,6 AS KINDNUMBER
,SUM(GRADUATE) AS AMT
FROM ENROLLMENT_STATUS AS ES
LEFT JOIN PREFECTURE AS PR
ON ES.PF_CODE  = PR.PF_CODE 
--INNER JOIN
--ON
WHERE SURVEY_YEAR='2020'
AND GRADUATE IS NOT NULL
GROUP BY SURVEY_YEAR 
,PF_NAME) AS K
ORDER BY K.PF_CODE asc
,K.KINDNUMBER