with v1 as
(
    select
        ES.SURVEY_YEAR as SV_YEAR
    ,   ES.PF_CODE as CODE
    ,   PRE.PF_NAME as PREFECTURE
    ,   sum(ES.ELEMENTARY) as ELE
    ,   sum(ES.MIDDLE) as MID
    ,   sum(ES.HIGH) as HIG
    ,   sum(ES.COLLEGE) as COL
    ,   sum(ES.JUNIOR_CLG) as JUN
    ,   sum(ES.GRADUATE) as GRA
    from
        ENROLLMENT_STATUS ES
    join
        PREFECTURE PRE
    on
        ES.PF_CODE = PRE.PF_CODE
    where
        SV_YEAR = '2020'
    group by
        ES.SURVEY_YEAR
    ,   PRE.PF_NAME
)

select
    v2.SV_YEAR
,   v2.PREFECTURE
,   v2.KIND
,   v2.AMT
from
(
    select
        SV_YEAR
    ,   CODE
    ,   PREFECTURE
    ,   '小学校' as KIND
    ,   ELE as AMT
    ,   1 as ORD
    from
        v1

    union

    select
        SV_YEAR
    ,   CODE
    ,   PREFECTURE
    ,   '中学校' as KIND
    ,   MID as AMT
    ,   2 as ORD
    from
        v1

    union

    select
        SV_YEAR
    ,   CODE
    ,   PREFECTURE
    ,   '高校' as KIND
    ,   HIG as AMT
    ,   3 as ORD
    from
        v1

    union

    select
        SV_YEAR
    ,   CODE
    ,   PREFECTURE
    ,   '短大' as KIND
    ,   JUN as AMT
    ,   4 as ORD
    from
        v1

    union

    select
        SV_YEAR
    ,   CODE
    ,   PREFECTURE
    ,   '大学' as KIND
    ,   COL as AMT
    ,   5 as ORD
    from
        v1

    union

    select
        SV_YEAR
    ,   CODE
    ,   PREFECTURE
    ,   '大学院' as KIND
    ,   GRA as AMT
    ,   6 as ORD
    from
        v1
) v2
where
    v2.AMT is not null
order by
    v2.CODE
,   v2.ORD