with t1 as (
select
 SURVEY_YEAR as SV_YEAR
 ,PF_CODE
 ,SUM(ELEMENTARY) as ele
 ,SUM(MIDDLE) as mid
 ,SUM(HIGH) as high
 ,SUM(JUNIOR_CLG) as jun
 ,SUM(COLLEGE) as col
 ,SUM(GRADUATE) as gra
from ENROLLMENT_STATUS 
where SURVEY_YEAR = 2020
group by 1,2
), t2 as (
    select
        SV_YEAR
        ,PF_CODE
        , '01_小学校' AS KIND
        , ele AS AMT
    from t1
    
    UNION ALL
    select
        SV_YEAR
        ,PF_CODE
        , '02_中学校' 
        , mid
    from t1
    where mid is not NULL
    
    UNION ALL
    select
        SV_YEAR
        ,PF_CODE
        , '03_高校' 
        , high
    from t1
    
    UNION ALL
    select
        SV_YEAR
        ,PF_CODE
        , '04_短大' 
        , jun
    from t1
    
    UNION ALL
    select
        SV_YEAR
        ,PF_CODE
        , '05_大学' 
        , col
    from t1
    
    UNION ALL
    select
        SV_YEAR
        ,PF_CODE
        , '06_大学院' 
        , gra
    from t1
) 
select 
    t2.SV_YEAR
    ,P.PF_NAME AS PREFECTURE
    ,substring(t2.KIND, 4) AS KIND
    ,t2.AMT
from t2 
inner join PREFECTURE P
on P.PF_CODE = t2.PF_CODE
where t2.AMT is not NULL order by t2.PF_CODE, t2.KIND