WITH t AS (
SELECT 
  SURVEY_YEAR AS SV_YEAR, 
  PF_CODE,
  '小学校' AS KIND,
  1 AS n,
  SUM(COALESCE(ELEMENTARY, 0)) AS AMT
FROM ENROLLMENT_STATUS
GROUP BY SURVEY_YEAR, PF_CODE
UNION ALL
SELECT 
  SURVEY_YEAR AS SV_YEAR, 
  PF_CODE,
  '中学校' AS KIND,
  2 AS n,
  SUM(COALESCE(MIDDLE, 0)) AS AMT
FROM ENROLLMENT_STATUS
GROUP BY SURVEY_YEAR, PF_CODE
UNION ALL
SELECT 
  SURVEY_YEAR AS SV_YEAR, 
  PF_CODE,
  '高校' AS KIND,
  3 AS n,
  SUM(COALESCE(HIGH, 0)) AS AMT
FROM ENROLLMENT_STATUS
GROUP BY SURVEY_YEAR, PF_CODE
UNION ALL
SELECT 
  SURVEY_YEAR AS SV_YEAR, 
  PF_CODE,
  '短大' AS KIND,
  4 AS n,
  SUM(COALESCE(JUNIOR_CLG, 0)) AS AMT
FROM ENROLLMENT_STATUS
GROUP BY SURVEY_YEAR, PF_CODE
UNION ALL
SELECT 
  SURVEY_YEAR AS SV_YEAR, 
  PF_CODE,
  '大学' AS KIND,
  5 AS n,
  SUM(COALESCE(COLLEGE, 0)) AS AMT
FROM ENROLLMENT_STATUS
GROUP BY SURVEY_YEAR, PF_CODE
UNION ALL
SELECT 
  SURVEY_YEAR AS SV_YEAR, 
  PF_CODE,
  '大学院' AS KIND,
  6 AS n,
  SUM(COALESCE(GRADUATE, 0)) AS AMT
FROM ENROLLMENT_STATUS
GROUP BY SURVEY_YEAR, PF_CODE
)
, t2 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 AND AMT > 0
ORDER BY p.PF_CODE, n
)
SELECT
  SV_YEAR,
  PREFECTURE,
  KIND,
  AMT
FROM t2
;