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

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

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;