with tbl as (
    select
        es.survey_year,
        p.pf_name,
        sum(case when elementary is not null then elementary else 0 end) as 'amt_e',
        sum(case when middle is not null then middle else 0 end) as 'amt_m',
        sum(case when high is not null then high else 0 end) as 'amt_h',
        sum(case when junior_clg is not null then junior_clg else 0 end) as 'amt_j',
        sum(case when college is not null then college else 0 end) as 'amt_c',
        sum(case when graduate is not null then graduate else 0 end) as 'amt_g'
    from ENROLLMENT_STATUS es
    join PREFECTURE p on es.pf_code = p.pf_code
    where es.survey_year = 2020
    group by es.pf_code
    
),
union_tbl as (
    select
        survey_year as 'SV_YEAR',
        pf_name as 'PREFECTURE',
        '小学校' as 'KIND',
        amt_e as 'AMT'
    from tbl
    union all
    select
        survey_year as 'SV_YEAR',
        pf_name as 'PREFECTURE',
        '中学校' as 'KIND',
        amt_m as 'AMT'
    from tbl
    union all
    select
        survey_year as 'SV_YEAR',
        pf_name as 'PREFECTURE',
        '高校' as 'KIND',
        amt_h as 'AMT'
    from tbl
    union all
    select
        survey_year as 'SV_YEAR',
        pf_name as 'PREFECTURE',
        '短大' as 'KIND',
        amt_j as 'AMT'
    from tbl
    union all
    select
        survey_year as 'SV_YEAR',
        pf_name as 'PREFECTURE',
        '大学' as 'KIND',
        amt_c as 'AMT'
    from tbl
    union all
    select
        survey_year as 'SV_YEAR',
        pf_name as 'PREFECTURE',
        '大学院' as 'KIND',
        amt_g as 'AMT'
    from tbl
)

select *
from tbl;

-- select *
-- from union_tbl
-- where AMT <> 0
-- order by 2 asc