Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Complex Query

Author  Topic 

vrupatel
Starting Member

1 Post

Posted - 2013-01-16 : 21:34:35
Hi,

I have below source data:

NEWKEY OLDKEY TIME
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:00AM
55 38 01/16/2013 12:09:00AM


We have a concept of stacking where a old key is stacked under a new key. In the above example 62 is stacked under 40, later 40 is stacked under 78 and then 78 is stacked under 68. So in short 68 is now a new key for old keys:62,40,78

I want the query output as:

NEWKEY OLDKEY TIME
51 42 01/16/2013 10:00:15AM
58 56 01/16/2013 10:00:58AM
68 62 01/16/2013 11:05:00AM
68 40 01/16/2013 11:33:00AM
68 78 01/16/2013 12:08:00AM
55 38 01/16/2013 12:09:00AM


Is this possible by writing a SQL statement or we will need to implement this via SP's or some complex functionality

Any inputs is very much appreciated


visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-16 : 23:28:50

Use recursive CTE
see illustration below



declare @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 StackDetail
AS
(
SELECT t1.NEWKEY,t1.OLDKEY,t1.[TIME],CAST(t1.NEWKEY AS varchar(11)) AS [KeyPath]
FROM @test t1
LEFT JOIN @test t2
ON t2.OLDKEY = t1.NEWKEY
WHERE t2.NEWKEY IS NULL
UNION ALL

SELECT t.NEWKEY,t.OLDKEY,t.[TIME],CAST(s.[KeyPath] AS varchar(5)) +CAST('\' AS char(1)) + CAST(t.NEWKEY AS varchar(5))
FROM StackDetail s
JOIN @test t
ON t.NEWKEY = s.OLDKEY
)

SELECT LEFT(KeyPath,CHARINDEX('\',KeyPath+'\')-1) AS NEWKEY,OLDKEY,[TIME]
FROM StackDetail
ORDER BY [TIME]

output
---------------------------------
NEWKEY OLDKEY TIME
51 42 2013-01-16 10:00:15.000
58 56 2013-01-16 10:00:58.000
68 62 2013-01-16 11:05:00.000
68 40 2013-01-16 11:33:00.000
68 78 2013-01-16 12:08:00.000
55 38 2013-01-16 12:09:00.000



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -