SELECT
  SURVEY_YEAR AS "SV_YEAR",
  PF_NAME AS "PREFECTURE",
  GR_NAME AS "KIND",
  SUM(GR_AMT) AS "AMT"
FROM
(
SELECT
  SURVEY_YEAR,
  PF_CODE,
  PF_NAME,
  AGE,
  b."index",
  CASE
    WHEN b."index" = 1 THEN '小学校'
    WHEN b."index" = 2 THEN '中学校'
    WHEN b."index" = 3 THEN '高校'
    WHEN b."index" = 4 THEN '短大'
    WHEN b."index" = 5 THEN '大学'    
  ELSE '大学院' END AS GR_NAME,
  CASE
    WHEN b."index" = 1 THEN ELEMENTARY
    WHEN b."index" = 2 THEN MIDDLE
    WHEN b."index" = 3 THEN HIGH
    WHEN b."index" = 4 THEN JUNIOR_CLG
    WHEN b."index" = 5 THEN COLLEGE
  ELSE GRADUATE END AS GR_AMT
FROM
(
SELECT
  es.*,
  pr.PF_NAME
FROM
  ENROLLMENT_STATUS AS es
LEFT OUTER JOIN
  PREFECTURE AS pr
ON
  es.PF_CODE = pr.PF_CODE
WHERE
  SURVEY_YEAR = '2020'
) AS c
CROSS JOIN
  (
    SELECT
      1 AS "index"
    UNION ALL
    SELECT
      2 AS "index"
    UNION ALL
    SELECT
      3 AS "index"
     UNION ALL
    SELECT
      4 AS "index"
      UNION ALL
    SELECT
      5 AS "index"
      UNION ALL
    SELECT
      6 AS "index"
  ) AS b
)
GROUP BY
  SURVEY_YEAR,
  PF_NAME,
  GR_NAME
HAVING
  AMT IS NOT NULL
ORDER BY
  PF_CODE,
  "index"