with vTbl as
            (select
                 es.SURVEY_YEAR 
                ,PF_CODE
                ,'小学校' KIND
                ,1 as rnk 
                ,sum(ELEMENTARY) AMT
                from ENROLLMENT_STATUS es
                where es.SURVEY_YEAR=2020
                and ELEMENTARY is not null
                group by
                 PF_CODE
            union all
                select
                 es.SURVEY_YEAR 
                ,PF_CODE
                ,'中学校' KIND
                ,2 as rnk 
                ,sum(MIDDLE) AMT
                from ENROLLMENT_STATUS es
                where MIDDLE is not null
                and es.SURVEY_YEAR=2020
                group by
                 PF_CODE
            union all
                select
                 es.SURVEY_YEAR 
                ,PF_CODE
                ,'高校' KIND
                ,3 as rnk 
                ,sum(HIGH) AMT
                from ENROLLMENT_STATUS es
                where HIGH is not null
                and es.SURVEY_YEAR=2020
                group by
                 PF_CODE
            union all
                select
                 es.SURVEY_YEAR 
                ,PF_CODE
                ,'短大' KIND
                ,4 as rnk 
                ,sum(JUNIOR_CLG) AMT
                from ENROLLMENT_STATUS es
                where JUNIOR_CLG>0
                and es.SURVEY_YEAR=2020
                group by
                 PF_CODE
            union all
                select
                 es.SURVEY_YEAR 
                ,PF_CODE
                ,'大学' KIND
                ,5 as rnk 
                ,sum(COLLEGE) AMT
                from ENROLLMENT_STATUS es
                where COLLEGE is not null
                and es.SURVEY_YEAR=2020
                group by
                 PF_CODE
            union all
                select
                 es.SURVEY_YEAR 
                ,PF_CODE
                ,'大学院' KIND
                ,6 as rnk 
                ,sum(GRADUATE) AMT
                from ENROLLMENT_STATUS es
                where GRADUATE is not null
                and es.SURVEY_YEAR=2020
                group by
                 PF_CODE
            )
            select 
             t.SURVEY_YEAR as SV_YEAR
            ,p.PF_NAME as PREFECTURE
            ,t.KIND as KIND
            ,t.AMT as AMT
            from vTbl t
            inner join PREFECTURE p
                on t.PF_CODE = p.PF_CODE
            order by 
              t.PF_CODE asc
             ,t.rnk