SELECT  
'2020' SV_YEAR
,b.PF_NAME
,CASE 
WHEN KIND_V=1 THEN '小学校'
WHEN KIND_V=2 THEN '中学校'
WHEN KIND_V=3 THEN '高校'
WHEN KIND_V=4 THEN '短大'
WHEN KIND_V=5 THEN '大学'
ELSE '大学院'
END KIND
,SUM(AMT) AMT 
FROM
(
    select 
    PF_CODE
    ,1 KIND_V
    ,IFNULL(ELEMENTARY,0) AMT
    from ENROLLMENT_STATUS
    where SURVEY_YEAR=2020
    and ELEMENTARY IS NOT NULL
    
    UNION ALL
    select 
    PF_CODE
    ,2
    ,IFNULL(MIDDLE,0)
    from ENROLLMENT_STATUS
    where SURVEY_YEAR=2020
    and MIDDLE IS NOT NULL
    UNION ALL
    select 
    PF_CODE
    ,3
    ,IFNULL(HIGH,0)
    from ENROLLMENT_STATUS
    where SURVEY_YEAR=2020
    and HIGH IS NOT NULL
    union all
    select 
    PF_CODE
    ,4
    ,IFNULL(JUNIOR_CLG,0)
    from ENROLLMENT_STATUS
    where SURVEY_YEAR=2020
    and JUNIOR_CLG IS NOT NULL
    
    union all
    select 
    PF_CODE
    ,5
    ,IFNULL(COLLEGE,0)
    from ENROLLMENT_STATUS
    where SURVEY_YEAR=2020
     and COLLEGE IS NOT NULL
     
    union all
    select 
    PF_CODE
    ,6
    ,IFNULL(GRADUATE,0)
    from ENROLLMENT_STATUS
    where SURVEY_YEAR=2020
    and GRADUATE IS NOT NULL
)a
INNER JOIN
PREFECTURE b
ON
a.PF_CODE =b.PF_CODE
GROUP BY 
a.PF_CODE
,KIND_V
ORDER BY 
a.PF_CODE
,KIND_V