WITH
S1 AS
(SELECT PF_CODE,SUM(IFNULL(ELEMENTARY,0)) AMT,'小学校' KIND,1 ODR
FROM ENROLLMENT_STATUS
WHERE SURVEY_YEAR = '2020'
GROUP BY PF_CODE),
S2 AS
(SELECT PF_CODE,SUM(IFNULL(MIDDLE,0)) AMT,'中学校' KIND,2 ODR
FROM ENROLLMENT_STATUS
WHERE SURVEY_YEAR = '2020'
GROUP BY PF_CODE),
S3 AS
(SELECT PF_CODE,SUM(IFNULL(HIGH,0)) AMT,'高校' KIND,3 ODR
FROM ENROLLMENT_STATUS
WHERE SURVEY_YEAR = '2020'
GROUP BY PF_CODE),
S4 AS
(SELECT PF_CODE,SUM(IFNULL(JUNIOR_CLG,0)) AMT,'短大' KIND,4 ODR
FROM ENROLLMENT_STATUS
WHERE SURVEY_YEAR = '2020'
GROUP BY PF_CODE),
S5 AS
(SELECT PF_CODE,SUM(IFNULL(COLLEGE,0)) AMT,'大学' KIND,5 ODR
FROM ENROLLMENT_STATUS
WHERE SURVEY_YEAR = '2020'
GROUP BY PF_CODE),
S6 AS
(SELECT PF_CODE,SUM(IFNULL(GRADUATE,0)) AMT,'大学院' KIND,6 ODR
FROM ENROLLMENT_STATUS
WHERE SURVEY_YEAR = '2020'
GROUP BY PF_CODE),
S AS
(SELECT PF_CODE,AMT,KIND,ODR FROM S1 UNION ALL
 SELECT PF_CODE,AMT,KIND,ODR FROM S2 UNION ALL
 SELECT PF_CODE,AMT,KIND,ODR FROM S3 UNION ALL
 SELECT PF_CODE,AMT,KIND,ODR FROM S4 UNION ALL
 SELECT PF_CODE,AMT,KIND,ODR FROM S5 UNION ALL
 SELECT PF_CODE,AMT,KIND,ODR FROM S6  
 )
 SELECT 
 2020 SV_YEAR
,P.PF_NAME  PREFECTURE
,S.KIND "KIND"
,S.AMT
FROM 
 S INNER JOIN PREFECTURE P
 ON S.PF_CODE = P.PF_CODE
 WHERE S.AMT IS NOT NULL
 AND S.AMT <> 0
 ORDER BY 2,S.ODR
;