WITH TEMP AS(  
SELECT   
	SURVEY_YEAR  
	,E.PF_CODE  
	,P.PF_NAME  
	,AGE  
	,ELEMENTARY  
	,MIDDLE  
	,HIGH  
	,JUNIOR_CLG  
	,COLLEGE  
	,GRADUATE  
FROM   
	ENROLLMENT_STATUS E  
	LEFT JOIN PREFECTURE P  
	ON E.PF_CODE = P.PF_CODE  
WHERE E.SURVEY_YEAR = 2020  
),  
K_SORT AS(  
SELECT   
	SURVEY_YEAR		AS "SV_YEAR"  
	,PF_CODE		AS "CODE"  
	,PF_NAME		AS "PREFECTURE"  
	,'小学校'		AS "KIND"  
	,SUM(ELEMENTARY)AS "AMT"  
FROM TEMP  
GROUP BY SURVEY_YEAR,PF_CODE,PF_NAME  
HAVING SUM(ELEMENTARY) IS NOT NULL  
  
UNION ALL  
SELECT   
	SURVEY_YEAR,PF_CODE,PF_NAME,'中学校',SUM(MIDDLE)  
FROM TEMP  
GROUP BY SURVEY_YEAR,PF_CODE,PF_NAME  
HAVING SUM(MIDDLE) IS NOT NULL  
  
UNION ALL  
SELECT   
	SURVEY_YEAR,PF_CODE,PF_NAME,'高校',SUM(HIGH)  
FROM TEMP  
GROUP BY SURVEY_YEAR,PF_CODE,PF_NAME  
HAVING SUM(HIGH) IS NOT NULL  
  
UNION ALL  
SELECT   
	SURVEY_YEAR,PF_CODE,PF_NAME,'短大',SUM(JUNIOR_CLG)  
FROM TEMP  
GROUP BY SURVEY_YEAR,PF_CODE,PF_NAME  
HAVING SUM(JUNIOR_CLG) IS NOT NULL  
  
UNION ALL  
SELECT   
	SURVEY_YEAR,PF_CODE,PF_NAME,'大学',SUM(COLLEGE)  
FROM TEMP  
GROUP BY SURVEY_YEAR,PF_CODE,PF_NAME  
HAVING SUM(COLLEGE) IS NOT NULL  
UNION ALL  
SELECT   
	SURVEY_YEAR,PF_CODE,PF_NAME,'大学院',SUM(GRADUATE)  
FROM TEMP  
GROUP BY SURVEY_YEAR,PF_CODE,PF_NAME  
HAVING SUM(GRADUATE) IS NOT NULL  
)  
SELECT SV_YEAR,PREFECTURE,KIND,AMT  
FROM K_SORT  
ORDER BY   
	CODE ASC  
	,CASE KIND WHEN '小学校'		THEN 1  
			   WHEN '中学校'		THEN 2  
			   WHEN '高校'		THEN 3  
			   WHEN '短大'		THEN 4  
			   WHEN '大学'		THEN 5  
			   ELSE 6   
		END ASC;