with
base as(
select ENROLLMENT_STATUS.*, PREFECTURE.PF_NAME
from ENROLLMENT_STATUS
left join PREFECTURE using(PF_CODE)
where SURVEY_YEAR = 2020
)
,uni as(
select 
    SURVEY_YEAR as SV_YEAR
    ,PF_NAME as PREFECTURE
    ,PF_CODE
    ,'小学校' as KIND
    ,0 as KIND_CODE
    , sum(ELEMENTARY) as AMT
from base 
group by SURVEY_YEAR, PF_NAME
union all
select 
    SURVEY_YEAR as SV_YEAR
    ,PF_NAME as PREFECTURE
    ,PF_CODE
    ,'中学校' as KIND
    ,1 as KIND_CODE
    , sum(MIDDLE) as AMT
from base 
group by SURVEY_YEAR, PF_NAME
union all
select 
    SURVEY_YEAR as SV_YEAR
    ,PF_NAME as PREFECTURE
    ,PF_CODE
    ,'高校' as KIND
    ,2 as KIND_CODE
    , sum(HIGH) as AMT
from base 
group by SURVEY_YEAR, PF_NAME
union all
select 
    SURVEY_YEAR as SV_YEAR
    ,PF_NAME as PREFECTURE
    ,PF_CODE
    ,'短大' as KIND
    ,3 as KIND_CODE
    , sum(JUNIOR_CLG) as AMT
from base 
group by SURVEY_YEAR, PF_NAME
union all
select 
    SURVEY_YEAR as SV_YEAR
    ,PF_NAME as PREFECTURE
    ,PF_CODE
    ,'大学' as KIND
    ,4 as KIND_CODE
    , sum(COLLEGE) as AMT
from base 
group by SURVEY_YEAR, PF_NAME
union all
select 
    SURVEY_YEAR as SV_YEAR
    ,PF_NAME as PREFECTURE
    ,PF_CODE
    ,'大学院' as KIND
    ,5 as KIND_CODE
    , sum(GRADUATE) as AMT
from base 
group by SURVEY_YEAR, PF_NAME
)
select 
    SV_YEAR
    ,PREFECTURE
    ,KIND
    ,AMT
from uni
where AMT is not null
order by 
    PF_CODE
    ,KIND_CODE
;