WITH K AS(

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 ES
LEFT JOIN PREFECTURE PR
ON ES.PF_CODE = PR.PF_CODE
WHERE SURVEY_YEAR='2020'
AND ELEMENTARY IS NOT NULL
GROUP BY SURVEY_YEAR
,ES.PF_CODE
,PF_NAME

UNION

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 ES
LEFT JOIN PREFECTURE PR
ON ES.PF_CODE = PR.PF_CODE
WHERE SURVEY_YEAR='2020'
AND MIDDLE IS NOT NULL
GROUP BY SURVEY_YEAR
,ES.PF_CODE
,PF_NAME

UNION

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 ES
LEFT JOIN PREFECTURE PR
ON ES.PF_CODE = PR.PF_CODE
WHERE SURVEY_YEAR='2020'
AND HIGH IS NOT NULL
GROUP BY SURVEY_YEAR
,ES.PF_CODE
,PF_NAME

UNION

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 ES
LEFT JOIN PREFECTURE PR
ON ES.PF_CODE = PR.PF_CODE
WHERE SURVEY_YEAR='2020'
AND JUNIOR_CLG IS NOT NULL
GROUP BY SURVEY_YEAR
,ES.PF_CODE
,PF_NAME

UNION

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 ES
LEFT JOIN PREFECTURE PR
ON ES.PF_CODE = PR.PF_CODE
WHERE SURVEY_YEAR='2020'
AND COLLEGE IS NOT NULL
GROUP BY SURVEY_YEAR
,ES.PF_CODE
,PF_NAME

UNION

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 ES
LEFT JOIN PREFECTURE PR
ON ES.PF_CODE = PR.PF_CODE
WHERE SURVEY_YEAR='2020'
AND GRADUATE IS NOT NULL
GROUP BY SURVEY_YEAR
,ES.PF_CODE
,PF_NAME
)

SELECT SV_YEAR AS SV_YEAR
,PREFECTURE AS PREFECTURE
,KIND AS KIND
,AMT AS AMT
FROM k
ORDER BY PF_CODE asc
,KINDNUMBER asc