-- SURVEY_YEAR → SV_YEAR
-- PF_NAME → PREFECTURE
-- 集計した就学先の種類 → KIND
-- 就学先ごとに集計した人数 → AMT

-- SURVEY_YEAR	PF_CODE	AGE	ELEMENTARY	MIDDLE	HIGH	JUNIOR_CLG	COLLEGE	GRADUATE

WITH t AS (
SELECT 
  SURVEY_YEAR AS SV_YEAR, 
  PF_CODE,
  '小学校' AS KIND,
  1 AS n,
  SUM(ELEMENTARY) 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(MIDDLE) 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(HIGH) 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(JUNIOR_CLG) 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(COLLEGE) 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(GRADUATE) 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
ORDER BY p.PF_CODE, n
)
SELECT
  SV_YEAR,
  PREFECTURE,
  KIND,
  AMT
FROM t2