-- https://topsic-contest.jp/contests/contest002/problems/contest002-4
WITH RECURSIVE seq(n) AS (
  VALUES (1)
  UNION ALL
  SELECT n + 1
  FROM seq
  WHERE n < 6
),
sub AS (
  SELECT SURVEY_YEAR,
    PF_CODE,
    n,
    CASE
      n
      WHEN 1 THEN '小学校'
      WHEN 2 THEN '中学校'
      WHEN 3 THEN '高校'
      WHEN 4 THEN '短大'
      WHEN 5 THEN '大学'
      WHEN 6 THEN '大学院'
    END AS KIND,
    CASE
      n
      WHEN 1 THEN ELEMENTARY
      WHEN 2 THEN MIDDLE
      WHEN 3 THEN HIGH
      WHEN 4 THEN JUNIOR_CLG
      WHEN 5 THEN COLLEGE
      WHEN 6 THEN GRADUATE
    END AS AMT
  FROM ENROLLMENT_STATUS
    JOIN seq
  WHERE SURVEY_YEAR = 2020
)
SELECT SURVEY_YEAR AS SV_YEAR,
  PF_NAME AS PREFECTURE,
  KIND,
  SUM(AMT) AS AMT
FROM sub
  JOIN PREFECTURE USING (PF_CODE)
WHERE AMT IS NOT NULL
GROUP BY SV_YEAR,
  PF_NAME,
  KIND
ORDER BY PF_CODE,
  n;