with KINDS(KIND, NBR) as (
    select '小学校', 1
    union all
    select  '中学校', 2
    union all 
    select '高校', 3
    union all
    select '短大', 4
    union all
    select '大学', 5
    union all
    select '大学院', 6
) , TARGET_DATA as (
    select
        P.PF_NAME
        , E.*
    from ENROLLMENT_STATUS as E
    inner join PREFECTURE as P on E.PF_CODE = P.PF_CODE
    where E.SURVEY_YEAR = 2020
)

select
    SV_YEAR
    , PREFECTURE
    , KIND
    , AMT
from (select
        T.SURVEY_YEAR as SV_YEAR
        , T.PF_CODE
        , T.PF_NAME as PREFECTURE
        , K.KIND as KIND
        , K.NBR
        , sum(case
            when K.KIND = '小学校' and ELEMENTARY is not null then ELEMENTARY
            when K.KIND = '中学校' and MIDDLE is not null then MIDDLE
            when K.KIND = '高校' and HIGH is not null then HIGH
            when K.KIND = '短大' and JUNIOR_CLG is not null then JUNIOR_CLG
            when K.KIND = '大学' and COLLEGE is not null then COLLEGE
            when K.KIND = '大学院' and GRADUATE is not null then GRADUATE
            else null
        end) as AMT
    from TARGET_DATA as T, KINDS as K
    group by SV_YEAR, PREFECTURE, KIND)
where AMT is not null
order by PF_CODE asc, NBR asc;