Q:

Sql Server Recursion

I have a table it has two columns.
CurrentNumber | NewNumber
12345 | 12346
12346 | 12347
12347 | NULL
12349 | NULL
12350 | 12351
12351 | NULL

What I'd like to be able to do is flatten this view as follows.  Create a new table
CurrentNumber | OldNumber
12347 | 12346
12347 | 12345
12351 | 12350

Here is my issue.  I can use some nested Cursors to find the number of recursions that may exist but I haven't been able to come up with a way to find the old numbers into infinity.  Any help would be appreciated.
I have Sql Server 2008, 2012, or a MySql server available to try and solve this problem.  I can also write an external app to potentially do this but I have to do this regularly so I'd rather keep it in SQL if possible.

A:

Try this. SQL Fiddle
CREATE TABLE t
    ([CurrentNumber] int, [NewNumber] int)
;

INSERT INTO t
    ([CurrentNumber], [NewNumber])
VALUES
    (12345, 12346),
    (12346, 12347),
    (12347, NULL),
    (12349, NULL),
    (12350, 12351),
    (12351, NULL)
;

;WITH cte AS (
  SELECT [CurrentNumber], CAST(NULL AS INT) AS OldNumber
  FROM t
  WHERE NewNumber IS NULL
  UNION ALL
  SELECT c.CurrentNumber, t.CurrentNumber AS OldNumber
  FROM t
  INNER JOIN cte c
      ON (ISNULL(c. OldNumber,c.CurrentNumber) = t.NewNumber)
)
SELECT * 
FROM cte
WHere OldNumber IS NOT NULL
ORDER BY 1,2    

