Sql Server Recursion

Question

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.

EricZ · Accepted Answer · 2012-12-13 19:02:42Z

1

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

answered Dec 13, 2012 at 19:02

EricZ

6,2151 gold badge33 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Stormflurry Over a year ago

This looks like the final answer. My basic testing seems to prove this correct. I'm able to follow a single number through all it's various super-cessions.

Jarry Bruckheimer · Accepted Answer · 2012-12-13 18:30:36Z

0

is that what you want ?

;with cte as
            (
              select * 
              from table 
              where NewNumber is not null 
            ),
       cte2 as 
            (
               select NewNumber CurrentNumber, CurrentNumber OldNumber 
               from cte 
            )

 select * from cte2 order by CurrentNumber

You can insert these result to a table if you want.

answered Dec 13, 2012 at 18:30

Jarry Bruckheimer

176 bronze badges

3 Comments

Stormflurry Over a year ago

This solution was so simple it blows my mind. Thank you. Just tested and the data appears correct and it's FAST!

EricZ Over a year ago

@Stormflurry, this query will give you result as CURRENTNUMBER 12346 OLDNUMBER 12345 , which is different than you expected CURRENTNUMBER 12347 OLDNUMBER 12345

Stormflurry Over a year ago

There appears to be one issue with this. Say a New number zig zags through an old number. So my example above where the current number is 12347 which superceeded 12346 which superceeded 12345. That scenario doesn't appear to be handled in this example.

Collectives™ on Stack Overflow

Sql Server Recursion

2 Answers 2

1 Comment

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Related