SELECT
SURVEY_YEAR AS SV_YEAR
,p.PF_NAME AS PREFECTURE

, CASE g.x 
  WHEN 1 THEN '小学校'
  WHEN 2 THEN '中学校'
  WHEN 3 THEN '高校'
  WHEN 4 THEN '短大'
  WHEN 5 THEN '大学'
  WHEN 6 THEN '大学院'
  END AS KIND

, CASE g.x 
  WHEN 1 THEN d.ELEMENTARY
  WHEN 2 THEN d.MIDDLE
  WHEN 3 THEN d.HIGH
  WHEN 4 THEN d.JUNIOR_CLG
  WHEN 5 THEN d.COLLEGE
  WHEN 6 THEN d.GRADUATE
  END AS AMT

FROM PREFECTURE p

CROSS JOIN (
WITH recursive
generate_series(x) AS (
 SELECT 1
 UNION ALL
 SELECT x+1 FROM generate_series LIMIT 6
) SELECT x FROM generate_series) AS g

CROSS JOIN (
SELECT
SURVEY_YEAR
,PF_CODE as PF_CODE_D
,SUM(IFNULL(ELEMENTARY, 0)) AS ELEMENTARY
,SUM(IFNULL(MIDDLE, 0)) AS MIDDLE
,SUM(IFNULL(HIGH, 0)) AS HIGH
,SUM(IFNULL(JUNIOR_CLG, 0)) AS JUNIOR_CLG
,SUM(IFNULL(COLLEGE, 0)) AS COLLEGE
,SUM(IFNULL(GRADUATE, 0)) AS GRADUATE
FROM ENROLLMENT_STATUS
GROUP BY SURVEY_YEAR, PF_CODE
) as d

WHERE p.PF_CODE = d.PF_CODE_D
AND d.SURVEY_YEAR = 2020
AND AMT > 0

ORDER BY p.PF_CODE ASC, g.x ASC
;