WITH TTT AS(
SELECT 
SURVEY_YEAR
,PF_CODE
,SUM(IFNULL(ELEMENTARY,0)) ELEMENTARY
,SUM(IFNULL(MIDDLE,0)) MIDDLE
,SUM(IFNULL(HIGH,0)) HIGH
,SUM(IFNULL(JUNIOR_CLG,0)) JUNIOR_CLG
,SUM(IFNULL(COLLEGE,0)) COLLEGE
,SUM(IFNULL(GRADUATE,0)) GRADUATE
FROM ENROLLMENT_STATUS
WHERE SURVEY_YEAR ='2020'
group BY SURVEY_YEAR,PF_CODE
)
SELECT 
SV_YEAR,
B.PF_NAME AS PREFECTURE,
KIND,AMT
FROM (
SELECT SURVEY_YEAR AS SV_YEAR,PF_CODE AS PREFECTURE,1 AS NUM,'小学校' AS KIND,ELEMENTARY  AS AMT FROM TTT
UNION ALL
SELECT SURVEY_YEAR,PF_CODE,2 AS NUM,'中学校',MIDDLE FROM TTT
UNION ALL
SELECT SURVEY_YEAR,PF_CODE,3 AS NUM,'高校',HIGH FROM TTT
UNION ALL
SELECT SURVEY_YEAR,PF_CODE,4 AS NUM,'短大',JUNIOR_CLG FROM TTT
UNION ALL
SELECT SURVEY_YEAR,PF_CODE,5 AS NUM,'大学',COLLEGE FROM TTT
UNION ALL
SELECT SURVEY_YEAR,PF_CODE,6 AS NUM,'大学院',GRADUATE FROM TTT
) A
INNER JOIN PREFECTURE B ON A.PREFECTURE =B.PF_CODE
WHERE IFNULL(AMT,0) <> 0
order BY PREFECTURE ASC,NUM