WITH kind_values (kind) AS (
  SELECT 'ELEMENTARY' UNION ALL
  SELECT 'MIDDLE' UNION ALL
  SELECT 'HIGH' UNION ALL
  SELECT 'JUNIOR_CLG' UNION ALL
  SELECT 'COLLEGE' UNION ALL
  SELECT 'GRADUATE'
),
enrollment AS (
  SELECT
    es.SURVEY_YEAR AS SV_YEAR,
    es.PF_CODE,
    pf.PF_NAME AS PREFECTURE,
    CASE
      WHEN kind = 'ELEMENTARY' THEN '小学校'
      WHEN kind = 'MIDDLE' THEN '中学校'
      WHEN kind = 'HIGH' THEN '高校'
      WHEN kind = 'JUNIOR_CLG' THEN '短大'
      WHEN kind = 'COLLEGE' THEN '大学'
      WHEN kind = 'GRADUATE' THEN '大学院'
    END AS KIND,
    CASE kind
      WHEN 'ELEMENTARY' THEN ELEMENTARY
      WHEN 'MIDDLE' THEN MIDDLE
      WHEN 'HIGH' THEN HIGH
      WHEN 'JUNIOR_CLG' THEN JUNIOR_CLG
      WHEN 'COLLEGE' THEN COLLEGE
      WHEN 'GRADUATE' THEN GRADUATE
    END AS amt
  FROM
    ENROLLMENT_STATUS es
    JOIN PREFECTURE pf ON es.PF_CODE = pf.PF_CODE
    CROSS JOIN kind_values
  WHERE
    es.SURVEY_YEAR = 2020
)
SELECT
  SV_YEAR,
  PREFECTURE,
  KIND,
  SUM(amt) AS AMT
FROM
  enrollment
GROUP BY
  SV_YEAR,
  PF_CODE,
  PREFECTURE,
  KIND
HAVING
  SUM(amt) IS NOT NULL
ORDER BY
  PF_CODE,
  CASE
    WHEN KIND = '小学校' THEN 1
    WHEN KIND = '中学校' THEN 2
    WHEN KIND = '高校' THEN 3
    WHEN KIND = '短大' THEN 4
    WHEN KIND = '大学' THEN 5
    WHEN KIND = '大学院' THEN 6
  END;