with population as (
    select
        survey_year,
        pf_code,
        sum(elementary) as sum1,
        sum(middle) as sum2,
        sum(high) as sum3,
        sum(junior_clg) as sum4,
        sum(college) as sum5,
        sum(graduate) as sum6
    from
        enrollment_status
    where
        survey_year = 2020
    group by
        pf_code
)
select
    survey_year as SV_YEAR,
    pf_name as PREFECTURE,
    kind_name as KIND,
    sum as AMT
from (
    select P.survey_year, PR.*, 1 as kind_id, '小学校' as kind_name, sum1 as sum
    from population P join prefecture PR using (pf_code)
    union all
    select P.survey_year, PR.*, 2, '中学校', sum2
    from population P join prefecture PR using (pf_code)
    union all
    select P.survey_year, PR.*, 3, '高校', sum3
    from population P join prefecture PR using (pf_code)
    union all
    select P.survey_year, PR.*, 4, '短大', sum4
    from population P join prefecture PR using (pf_code)
    union all
    select P.survey_year, PR.*, 5, '大学', sum5
    from population P join prefecture PR using (pf_code)
    union all
    select P.survey_year, PR.*, 6, '大学院', sum6
    from population P join prefecture PR using (pf_code)
)
where
    sum is not null
order by
    pf_code,
    kind_id
;