WITH  SEQ_TBL AS(
	SELECT
		1 AS SEQ_NO
	UNION ALL
	SELECT
		SEQ_NO + 1
	FROM
		SEQ_TBL
	WHERE
		SEQ_NO < 6
)
SELECT
	tmp.SURVEY_YEAR AS SV_YEAR
	,p.PF_NAME  AS PREFECTURE
	,tmp.KIND AS KIND
	,SUM(tmp.AMOUNT) AS AMT
FROM
	(
		SELECT
			es.SURVEY_YEAR
			,es.PF_CODE
			,st.SEQ_NO
			,CASE st.SEQ_NO
				WHEN 1 THEN '小学校' 
				WHEN 2 THEN '中学校' 
				WHEN 3 THEN '高校' 
				WHEN 4 THEN '短大' 
				WHEN 5 THEN '大学' 
				WHEN 6 THEN '大学院'
				ELSE NULL
				END AS KIND
			,CASE  st.SEQ_NO
				WHEN 1 THEN es.ELEMENTARY 
                WHEN 2 THEN es.MIDDLE 
                WHEN 3 THEN es.HIGH 
                WHEN 4 THEN es.JUNIOR_CLG 
                WHEN 5 THEN es.COLLEGE 
                WHEN 6 THEN es.GRADUATE 
                ELSE NULL 
                END AS AMOUNT
		FROM
			ENROLLMENT_STATUS AS es
			CROSS JOIN SEQ_TBL AS ST
		WHERE
			es.SURVEY_YEAR = 2020
	)AS tmp
	INNER JOIN PREFECTURE AS p
		ON p.PF_CODE = tmp.PF_CODE
WHERE
	 tmp.AMOUNT IS NOT NULL
GROUP BY
	tmp.SURVEY_YEAR
	,tmp.PF_CODE
	,tmp.KIND
ORDER BY
	tmp.PF_CODE ASC
	,tmp.SEQ_NO ASC;