I need to group a set of rows based on the Category column, and also limit the combined rows based on the SUM(Number) column to be less than or equal to the @Limit value.

For each distinct Category column I need to identify "buckets" that are <=@limit. If the SUM(Number) of all the rows for a Category column are <=@Limit then there will be only 1 bucket for that Category value (like 'CCCC' in the sample data). However if the SUM(Number)>@limit, then there will be multiple bucket rows for that Category value (like 'AAAA' in the sample data), and each bucket must be <=@Limit. There can be as many buckets as necessary. Also, look at Category value 'DDDD', its one row is greater than @Limit all by itself, and gets split into two rows in the result set.

Given this simplified data:

DECLARE @Detail table (DetailID int  primary key, Category char(4), Number int)
SET NOCOUNT ON
INSERT @Detail VALUES ( 1, 'AAAA',100)
INSERT @Detail VALUES ( 2, 'AAAA', 50)
INSERT @Detail VALUES ( 3, 'AAAA',300)
INSERT @Detail VALUES ( 4, 'AAAA',200)
INSERT @Detail VALUES ( 5, 'BBBB',500)
INSERT @Detail VALUES ( 6, 'CCCC',200)
INSERT @Detail VALUES ( 7, 'CCCC',100)
INSERT @Detail VALUES ( 8, 'CCCC', 50)
INSERT @Detail VALUES ( 9, 'DDDD',800)
INSERT @Detail VALUES (10, 'EEEE',100)
SET NOCOUNT OFF

DECLARE @Limit int
SET @Limit=500

I need one of these result set:

DetailID  Bucket  |    DetailID  Category Bucket
--------  ------  |    --------  -------- ------
 1        1       |     1        'AAAA'   1     
 2        1       |     2        'AAAA'   1     
 3        1       |     3        'AAAA'   1     
 4        2       |     4        'AAAA'   2     
 5        3       OR    5        'BBBB'   1     
 6        4       |     6        'CCCC'   1     
 7        4       |     7        'CCCC'   1     
 8        4       |     8        'CCCC'   1     
 9        5       |     9        'DDDD'   1     
 9        6       |     9        'DDDD'   2     
10        7       |    10        'EEEE'   1