SELECT
  '2020' SV_YEAR
  , PR.PF_NAME PREFECTURE
  , PF.KIND KIND
  , PF.AMT AMT
FROM
(
SELECT
 PF_CODE
 , SUM(ELEMENTARY) AMT
 , '小学校' KIND
 , 1 as num
FROM
 ENROLLMENT_STATUS
WHERE
 SURVEY_YEAR = '2020'
GROUP BY
 PF_CODE
UNION
SELECT
 PF_CODE
 , SUM(MIDDLE) AMT
 , '中学校' KIND
 , 2 as num
FROM
 ENROLLMENT_STATUS
WHERE
 SURVEY_YEAR = '2020'
GROUP BY
 PF_CODE
UNION
SELECT
 PF_CODE
 , SUM(HIGH) AMT
 , '高校' KIND
 , 3 as num
FROM
 ENROLLMENT_STATUS
WHERE
 SURVEY_YEAR = '2020'
GROUP BY
 PF_CODE
UNION
SELECT
 PF_CODE
 , SUM(JUNIOR_CLG) AMT
 , '短大' KIND
 , 4 as num
FROM
 ENROLLMENT_STATUS
WHERE
 SURVEY_YEAR = '2020'
GROUP BY
 PF_CODE
UNION
SELECT
 PF_CODE
 , SUM(COLLEGE) AMT	
 , '大学' KIND
 , 5 as num
FROM
 ENROLLMENT_STATUS
WHERE
 SURVEY_YEAR = '2020'
GROUP BY
 PF_CODE
UNION
SELECT
 PF_CODE
 , SUM(GRADUATE) AMT
 , '大学院' KIND
 , 6 as num
FROM
 ENROLLMENT_STATUS
WHERE
 SURVEY_YEAR = '2020'
GROUP BY
 PF_CODE
) PF
INNER JOIN PREFECTURE PR on PR.PF_CODE = PF.PF_CODE
WHERE
  PF.AMT is not NULL
ORDER BY
  PF.PF_CODE
  , PF.num
;