WITH t AS (
SELECT 
  SURVEY_YEAR AS SV_YEAR, 
  PF_CODE,
  '小学校' AS KIND,
  1 AS n,
  SUM(ELEMENTARY) AS AMT
FROM ENROLLMENT_STATUS
WHERE ELEMENTARY IS NOT NULL
GROUP BY SURVEY_YEAR, PF_CODE
UNION ALL
SELECT 
  SURVEY_YEAR AS SV_YEAR, 
  PF_CODE,
  '中学校' AS KIND,
  2 AS n,
  SUM(MIDDLE) AS AMT
FROM ENROLLMENT_STATUS
WHERE MIDDLE IS NOT NULL
GROUP BY SURVEY_YEAR, PF_CODE
UNION ALL
SELECT 
  SURVEY_YEAR AS SV_YEAR, 
  PF_CODE,
  '高校' AS KIND,
  3 AS n,
  SUM(HIGH) AS AMT
FROM ENROLLMENT_STATUS
WHERE HIGH IS NOT NULL
GROUP BY SURVEY_YEAR, PF_CODE
UNION ALL
SELECT 
  SURVEY_YEAR AS SV_YEAR, 
  PF_CODE,
  '短大' AS KIND,
  4 AS n,
  SUM(JUNIOR_CLG) AS AMT
FROM ENROLLMENT_STATUS
WHERE JUNIOR_CLG IS NOT NULL
GROUP BY SURVEY_YEAR, PF_CODE
UNION ALL
SELECT 
  SURVEY_YEAR AS SV_YEAR, 
  PF_CODE,
  '大学' AS KIND,
  5 AS n,
  SUM(COLLEGE) AS AMT
FROM ENROLLMENT_STATUS
WHERE COLLEGE IS NOT NULL
GROUP BY SURVEY_YEAR, PF_CODE
UNION ALL
SELECT 
  SURVEY_YEAR AS SV_YEAR, 
  PF_CODE,
  '大学院' AS KIND,
  6 AS n,
  SUM(GRADUATE) AS AMT
FROM ENROLLMENT_STATUS
WHERE GRADUATE IS NOT NULL
GROUP BY SURVEY_YEAR, PF_CODE
)
, ttt AS (
SELECT
  SV_YEAR,
  p.PF_CODE,
  PF_NAME AS PREFECTURE,
  KIND AS KIND,
  AMT AS AMT,
  n
FROM t
LEFT JOIN PREFECTURE AS p
  ON t.PF_CODE = p.PF_CODE
WHERE SV_YEAR = 2020 AND AMT is not NULL
ORDER BY p.PF_CODE, n
)


SELECT
  SV_YEAR,
  PREFECTURE,
  KIND,
  AMT
FROM ttt
;