WITH SEQ_TBL AS(
SELECT 
1 AS SEQ_NO
UNION ALL
SELECT 
SEQ_NO + 1
FROM SEQ_TBL
WHERE SEQ_TBL < 6
)


SELECT
ES_SUB.SURVEY_YEAR AS SV_YEAR,
PF.PF_NAME AS PREFECTURE,
ES_SUB.KIND AS KIND,
SUM(ES_SUB.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 ES_SUB
INNER JOIN PREFECTURE AS PF
 ON ES_SUB.PF_CODE = PF.PF_CODE
 
WHERE ES_SUB.AMOUNT IS NOT NULL

GROUP BY 
ES_SUB.SURVEY_YEAR,
PF.PF_NAME,
ES_SUB.KIND

ORDER BY
ES_SUB.PF_CODE ASC,
ES_SUB.SEQ_NO ASC;