Recursion in T-SQL

Question

Recursion in T-SQL

rockstar283

SSCrazy

Points: 2704
More actions
July 22, 2011 at 3:42 pm

#244557

I have a table XYZ with following data..
create table XYZ(parent_node_id int, child_id int)
insert into XYZ select 0000000000,0000000001
insert into XYZ select 0000000000,0000000124
insert into XYZ select 0000000000,0000007953
insert into XYZ select 0000007953,0000007954
insert into XYZ select 0000007954,0000007955
insert into XYZ select 0000007954,0000007956
insert into XYZ select 0000007957,0000007958
insert into XYZ select 0000007958,0000007959
insert into XYZ select 0000007958,0000007960
insert into XYZ select 0000007958,0000007961
The result should be
0000000000
------------0000000001
------------0000000124
------------0000007953
------------------------0000007954
------------------------------------0000007955
------------------------------------0000007956
0000007953
------------0000007954
0000007954
------------0000007955
------------0000007956
0000007957
------------0000007958
------------------------0000007959
------------------------0000007960
------------------------0000007961
0000007958
------------0000007959
------------0000007960
------------0000007961
I hope you are getting my question..
Its a recursive result where we first start with first parent_id..we select its child_id..then we make those child id as parent id and try to find their own child..
I think this can be done by recursive CTE..but I am not able to figure it out..can you guys please help me here..Thanks in advance

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

Take a look at the first link in my signature line below and post some readily consumable data THAT way, someone will likely jump on this right away.

The examples for "recursive CTE's" in Books Online are quite simple, though. All you'd really need to do is substitute a table name and a couple of column names. Try it. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Jeff Moden (7/22/2011)
Take a look at the first link in my signature line below and post some readily consumable data THAT way, someone will likely jump on this right away.
The examples for "recursive CTE's" in Books Online are quite simple, though. All you'd really need to do is substitute a table name and a couple of column names. Try it. 😉

Agreed on all accounts;-)

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 3

Also see answers here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=163395 and here http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/9572438c-cf3c-456c-8076-97e7a5e931b6.

N 56°04'39.16"
E 12°55'05.25"

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

@rockstar283,

According to the links SwePeso provided, you already received an answer a half hour after you posted. It would be the right thing to do if you let people know so they don't waste the time solving a problem that you already have an answer for. Personally, it makes me think twice about answering any of your future questions. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rockstar283 SSCrazy Points: 2704 More actions · Answer 5

Thank you guys for your help and Jeff Moden for ultra-extraordinary explanation of the basics of asking questions in forums..I was completely unaware of that..That was really helpful..I appreciate that..was away from Internet..so couldnt mark the answer..Thank you SwePaso for wonderful help..

ghanshyam.kundu Hall of Fame Points: 3337 More actions · Answer 6

i just took a chance

create table XYZ(parent_node_id int, child_id int)

insert into XYZ select 0000000000,0000000001

insert into XYZ select 0000000000,0000000124

insert into XYZ select 0000000000,0000007953

insert into XYZ select 0000007953,0000007954

insert into XYZ select 0000007954,0000007955

insert into XYZ select 0000007954,0000007956

insert into XYZ select 0000007957,0000007958

insert into XYZ select 0000007958,0000007959

insert into XYZ select 0000007958,0000007960

insert into XYZ select 0000007958,0000007961

ALTER PROC dbo.Show_Hierarchy

(

@Root int

)

AS

BEGIN

SET NOCOUNT ON

DECLARE @child_id int

SET @child_id = (SELECT child_id FROM dbo.XYZ WHERE child_id = @Root)

PRINT REPLICATE('-', @@NESTLEVEL * 4) + CAST(@child_id AS VARCHAR(50))

SET @child_id = (SELECT MIN(child_id) FROM dbo.XYZ WHERE parent_node_id = @Root)

WHILE @child_id IS NOT NULL

BEGIN

EXEC dbo.Show_Hierarchy @child_id

SET @child_id = (SELECT MIN(child_id) FROM dbo.XYZ WHERE parent_node_id = @Root AND child_id > @child_id)

END

GO

--EXEC dbo.Show_Hierarchy 0000000000

GO

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

ghanshyam.kundu (7/25/2011)
i just took a chance

Interesting way to do the same as Celko's infamous "push stack" conversion. Thanks for posting it.

I have code that will generate a million node "pure hierarchy" and, when I get a chance, I'll see what the performance might like.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)