Merging data in a single SQL table without a Cursor

The key observation is that a sequence of numbers minus another sequence is a constant. We can generate another sequence using row_number. This identifies all the groups:

select id, MIN(number) as low, MAX(number) as highfrom (select t.*,             (number - ROW_NUMBER() over (partition by id order by number) ) as groupnum      from t     ) tgroup by id, groupnum

The rest is just aggregation.

Solution with CTE and recursion:

WITH CTE AS (  SELECT T.ID, T.NUMBER, T.NUMBER AS GRP  FROM T   LEFT OUTER JOIN T T2 ON T.ID = T2.ID AND T.NUMBER -1 = T2.NUMBER   WHERE T2.ID IS NULL  UNION  ALL  SELECT T.ID, T.NUMBER, GRP  FROM CTE   INNER JOIN T  ON T.ID = CTE.ID AND T.NUMBER  = CTE.NUMBER + 1)SELECT ID, MAX( NUMBER ), MIN(NUMBER)FROM CTEGROUP BY ID, GRP

Results at fiddlesql

I'd suggest using a WHILE loop structure with a table variable instead of the cursor.

For example,

DECLARE @TableVariable TABLE(    MyID int IDENTITY (1, 1) PRIMARY KEY NOT NULL,    [ID] int,    [Number] int)DECLARE @Count int, @Max intINSERT INTO @TableVariable (ID, Number)SELECT ID, NumberFROM YourSourceTableSELECT @Count = 1, @Max = MAX(MyID)FROM @TableVariableWHILE @Count <= @MaxBEGIN    ...do your processing here...    SET @Count = @Count + 1END