July 22, 2011 at 3:42 pm
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
July 22, 2011 at 4:03 pm
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
Change is inevitable... Change for the better is not.
July 22, 2011 at 4:10 pm
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
July 22, 2011 at 11:35 pm
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"
July 23, 2011 at 10:26 am
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
Change is inevitable... Change for the better is not.
July 24, 2011 at 11:41 am
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..
July 25, 2011 at 12:48 am
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
END
GO
--EXEC dbo.Show_Hierarchy 0000000000
GO
July 25, 2011 at 4:49 am
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
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply