-- https://topsic-contest.jp/contests/contest002/problems/contest002-4


-- https://topsic-contest.jp/contests/contest002/problems/contest002-4
with base as(
    select
        e.pf_code,
        e.SURVEY_YEAR as SV_YEAR,
        p.pf_name as PREFECUTRE,
        sum(case when ELEMENTARY is null then 0 else ELEMENTARY end) as ELEMENTARY,
        sum(case when Middle is null then 0 else Middle end) as MIDDLE,
        sum(case when high is null then 0 else high end) as HIGH,
        sum(case when JUNIOR_CLG is null then 0 else JUNIOR_CLG end) as JUNIOR_CLG,
        sum(case when COLLEGE is null then 0 else COLLEGE end) as COLLEGE,
        sum(case when GRADUATE is null then 0 else GRADUATE end) as GRADUATE
    from
        ENROLLMENT_STATUS as e
        left join
            prefecture as p
        on  e.pf_code = p.pf_code
    where
        e.SURVEY_YEAR = 2020
    --and p.pf_name in ('徳島県', '香川県')
    group by
        e.SURVEY_YEAR,
        p.pf_name
),
year_pref as(
    select distinct
    pf_code,
        SV_YEAR,
        PREFECUTRE
    from
        base
        order by pf_code
),
result as (
select
    base_ele.pf_code,
    year_pref.SV_YEAR,
    year_pref.PREFECUTRE,
    '小学生' as KIND,
    base_ele.ELEMENTARY as AMT,
    1 as kind_order
from
    year_pref
    left join
        base as base_ele
    on  year_pref.SV_YEAR = base_ele.SV_YEAR
    and year_pref.PREFECUTRE = base_ele.PREFECUTRE
    where base_ele.ELEMENTARY > 0
    union all 
select
base_ele.pf_code,
    year_pref.SV_YEAR,
    year_pref.PREFECUTRE,
    '中学生' as KIND,
    base_ele.MIDDLE as AMT,
    2 as kind_order
from
    year_pref
    left join
        base as base_ele
    on  year_pref.SV_YEAR = base_ele.SV_YEAR
    and year_pref.PREFECUTRE = base_ele.PREFECUTRE
    where base_ele.MIDDLE > 0
union all 
select
base_ele.pf_code,
    year_pref.SV_YEAR,
    year_pref.PREFECUTRE,
    '高校生' as KIND,
    base_ele.HIGH as AMT,
    3 as kind_order
from
    year_pref
    left join
        base as base_ele
    on  year_pref.SV_YEAR = base_ele.SV_YEAR
    and year_pref.PREFECUTRE = base_ele.PREFECUTRE
    where base_ele.HIGH> 0
union all 
select
base_ele.pf_code,
    year_pref.SV_YEAR,
    year_pref.PREFECUTRE,
    '短大' as KIND,
    base_ele.JUNIOR_CLG as AMT,
    4 as kind_order
from
    year_pref
    left join
        base as base_ele
    on  year_pref.SV_YEAR = base_ele.SV_YEAR
    and year_pref.PREFECUTRE = base_ele.PREFECUTRE
    where base_ele.JUNIOR_CLG> 0
union all 
select
base_ele.pf_code,
    year_pref.SV_YEAR,
    year_pref.PREFECUTRE,
    '大学' as KIND,
    base_ele.COLLEGE as AMT,
    5 as kind_order
from
    year_pref
    left join
        base as base_ele
    on  year_pref.SV_YEAR = base_ele.SV_YEAR
    and year_pref.PREFECUTRE = base_ele.PREFECUTRE
    where base_ele.COLLEGE > 0
union all 
select
base_ele.pf_code,
    year_pref.SV_YEAR,
    year_pref.PREFECUTRE,
    '大学院' as KIND,
    base_ele.GRADUATE as AMT,
    6 as kind_order
    
from
    year_pref
    left join
        base as base_ele
    on  year_pref.SV_YEAR = base_ele.SV_YEAR
    and year_pref.PREFECUTRE = base_ele.PREFECUTRE
    where base_ele.GRADUATE > 0

)
select SV_YEAR, PREFECUTRE, KIND, AMT from result order by pf_code asc, kind_order asc;