WITH
S1 AS
(SELECT PF_CODE,SUM(ELEMENTARY) AMT,'小学校' KIND,1 ODR
FROM ENROLLMENT_STATUS
WHERE SURVEY_YEAR = '2020'
GROUP BY PF_CODE),
S2 AS
(SELECT PF_CODE,SUM(MIDDLE) AMT,'中学校' KIND,2 ODR
FROM ENROLLMENT_STATUS
WHERE SURVEY_YEAR = '2020'
GROUP BY PF_CODE),
S3 AS
(SELECT PF_CODE,SUM(HIGH) AMT,'高校' KIND,3 ODR
FROM ENROLLMENT_STATUS
WHERE SURVEY_YEAR = '2020'
GROUP BY PF_CODE),
S4 AS
(SELECT PF_CODE,SUM(JUNIOR_CLG) AMT,'短大' KIND,4 ODR
FROM ENROLLMENT_STATUS
WHERE SURVEY_YEAR = '2020'
GROUP BY PF_CODE),
S5 AS
(SELECT PF_CODE,SUM(COLLEGE) AMT,'大学' KIND,5 ODR
FROM ENROLLMENT_STATUS
WHERE SURVEY_YEAR = '2020'
GROUP BY PF_CODE),
S6 AS
(SELECT PF_CODE,SUM(GRADUATE) 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  "AMT"
FROM 
 S INNER JOIN PREFECTURE P
 ON S.PF_CODE = P.PF_CODE
 WHERE S.AMT IS NOT NULL
 ORDER BY 2,S.ODR
;