Sum rows not in the top x rows of a group in the table into an "other" row?

Question

If I have a table like the following which is the number of records with a given label grouped by a certain category (in this case Male, Female),

Number           Label     Category
51               A         M     
43               B         M   
22               C         M 
9                D         M  
6                E         M   
3                X         M
2                Y         M
2                Z         M
40               A         F
37               B         F
34               C         F
17               D         F
12               E         F
6                X         F
3                Y         F
1                Z         F

How would I transform it into a table that keeps the top x (e.g. 5 records) for each category, when the rows are sorted by number descending, and sums the remaining rows for that category into an "other" row? The desired output (assuming we are keeping the top 5) is below:

Number           Label     Category
51               A         M     
43               B         M   
22               C         M 
9                D         M  
6                E         M   
7                Other     M
40               A         F
37               B         F
34               C         F
17               D         F
12               E         F
10               Other     F

Answer 1

You can use window functions and aggregation:

select sum(t.number) as number, v.label, t.category
from (select t.*, row_number() over (partition by category order by number desc) as seqnum
      from t
     ) t cross appy
     (values (case when seqnum <= 5 then label else 'Other' end)
     ) v(label)
group by v.label, t.category;

Answer 2

You can use row_number() and aggregation:

select sum(number) number, new_label label, category
from (
    select
        t.*,
        case 
            when row_number() over(partition by category order by number) <= 5
            then label
            else 'Other'
        end new_label
    from mytable
) t
group by new_label, category
order by category, number desc