with raw as
(
select
    *
from
    ENROLLMENT_STATUS
where
    SURVEY_YEAR = 2020
),
dat as
(
select
    SURVEY_YEAR
    ,PF_CODE
    ,"1.小学校" as "KIND"
    ,ELEMENTARY as "AMT"
from
    raw
union all
select
    SURVEY_YEAR
    ,PF_CODE
    ,"2.中学校" as "KIND"
    ,MIDDLE as "AMT"
from
    raw
union all
select
    SURVEY_YEAR
    ,PF_CODE
    ,"3.高校" as "KIND"
    ,HIGH as "AMT"
from
    raw
union all
select
    SURVEY_YEAR
    ,PF_CODE
    ,"4.短大" as "KIND"
    ,JUNIOR_CLG as "AMT"
from
    raw
union all
select
    SURVEY_YEAR
    ,PF_CODE
    ,"5.大学" as "KIND"
    ,COLLEGE as "AMT"
from
    raw
union all
select
    SURVEY_YEAR
    ,PF_CODE
    ,"6.大学院" as "KIND"
    ,GRADUATE as "AMT"
from
    raw
)


select
    SV_YEAR
    ,PREFECTURE
    ,KIND
    ,AMT
from
    (
    select
        SURVEY_YEAR as SV_YEAR
        ,PF_NAME as PREFECTURE
        ,KIND as KINDwithN
        ,substr(KIND, 3) as KIND
        ,sum(AMT) as AMT
    from
        dat
        left join 
        PREFECTURE
        using(PF_CODE)
    where
        AMT is not null
    group by
        SURVEY_YEAR, PF_CODE, KIND
    order by
        PF_CODE asc, KINDwithN asc
    )