with t as (
  SELECT * FROM ENROLLMENT_STATUS WHERE SURVEY_YEAR = 2020
)
,piv as (
  SELECT SURVEY_YEAR, pf_code, 1 as n, '小学校' as KIND, SUM(ELEMENTARY) as AMT FROM t GROUP BY SURVEY_YEAR, pf_code
  UNION ALL
  SELECT SURVEY_YEAR, pf_code, 2 as n, '中学校' as KIND, SUM(MIDDLE) as AMT FROM t GROUP BY SURVEY_YEAR, pf_code
  UNION ALL
  SELECT SURVEY_YEAR, pf_code, 3 as n, '高校校' as KIND, SUM(HIGH) as AMT FROM t GROUP BY SURVEY_YEAR, pf_code
  UNION ALL
  SELECT SURVEY_YEAR, pf_code, 4 as n, '短大' as KIND, SUM(JUNIOR_CLG) as AMT FROM t GROUP BY SURVEY_YEAR, pf_code
  UNION ALL
  SELECT SURVEY_YEAR, pf_code, 5 as n, '大学' as KIND, SUM(COLLEGE) as AMT FROM t GROUP BY SURVEY_YEAR, pf_code
  UNION ALL
  SELECT SURVEY_YEAR, pf_code, 6 as n, '大学院' as KIND, SUM(GRADUATE) as AMT FROM t GROUP BY SURVEY_YEAR, pf_code
)
SELECT
A.SURVEY_YEAR AS SV_YEAR
,b.PF_NAME AS PREFECTURE
,A.KIND
,A.AMT
FROM
piv A
inner join
prefecture B 
USING (pf_code)
WHERE
A.AMT IS NOT NULL
ORDER BY
A.PF_CODE asc
,A.n asc
;