Recursive CTE Sample (Multiple entry points)


  

  Create TABLE test (ID int NOT NULL PRIMARY KEY,Check_GKey int NULL,GKey int NULL)

INSERT INTO test(ID,GKey) VALUES (8,7),
(11	,7),
(40	,24),
(7	,7),
(24	,6),
(1	,1),
(99	,8),
(23	,2),
(2	,2),
(45	,45),
(6,6)

;WITH rmycte AS
(
SELECT ID,GKey as Check_GKey, GKey FROM test t
WHERE  EXISTS(SELECT 1 FROM test WHERE ID<=t.ID AND GKey=t.ID)
UNION ALL
SELECT t.ID, r.Check_GKey,T.GKey FROM rmycte r JOIN test t 
ON r.ID=t.GKey AND r.ID<>t.ID
)
SELECT * FROM rmycte 
ORDER BY ID

Drop table test

/*
ID	Check_GKey	GKey
1	1	1
2	2	2
6	6	6
7	7	7
8	7	7
11	7	7
23	2	2
24	6	6
40	6	24
45	45	45
99	7	8

*/

 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s