Use recursive CTEsee illustration belowdeclare @test table(NEWKEY int, OLDKEY int, [TIME] datetime ) insert @test values(51, 42, '01/16/2013 10:00:15AM'), (58, 56, '01/16/2013 10:00:58AM'),(40, 62, '01/16/2013 11:05:00AM'),(78, 40, '01/16/2013 11:33:00AM'),(68, 78, '01/16/2013 12:08:00PM'),(55, 38, '01/16/2013 12:09:00PM');With StackDetailAS(SELECT t1.NEWKEY,t1.OLDKEY,t1.[TIME],CAST(t1.NEWKEY AS varchar(11)) AS [KeyPath]FROM @test t1LEFT JOIN @test t2ON t2.OLDKEY = t1.NEWKEYWHERE t2.NEWKEY IS NULLUNION ALLSELECT t.NEWKEY,t.OLDKEY,t.[TIME],CAST(s.[KeyPath] AS varchar(5)) +CAST('\' AS char(1)) + CAST(t.NEWKEY AS varchar(5))FROM StackDetail sJOIN @test tON t.NEWKEY = s.OLDKEY)SELECT LEFT(KeyPath,CHARINDEX('\',KeyPath+'\')-1) AS NEWKEY,OLDKEY,[TIME]FROM StackDetailORDER BY [TIME]output---------------------------------NEWKEY OLDKEY TIME51 42 2013-01-16 10:00:15.00058 56 2013-01-16 10:00:58.00068 62 2013-01-16 11:05:00.00068 40 2013-01-16 11:33:00.00068 78 2013-01-16 12:08:00.00055 38 2013-01-16 12:09:00.000
------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/