SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Complex Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vrupatel
Starting Member

India
1 Posts

Posted - 01/16/2013 :  21:34:35  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/16/2013 :  23:28:50  Show Profile  Reply with Quote

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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000