More recursive questions

I have a list of categories that I have to find the path to each
my table is set up like
ID CategoryNum CategoryName ParentID
1 AA Top Level 1 0
2 AB Top Level 2 0
3 BA Second Level 1 1
I need my query/stored proc to return
Tope Level 1/Second Level 1 if I pass in the integer of 3
I can do this in programming but cant seem to wrap my head around it in SQL Server
TIA for the helpHi,
Is your requirement to get a Tree structure from SQL Server?
SQL Server has got FOR XML clause which will return the data in hierarchial manner. But it does have implementaiton in returning n-level tree. You have to use EXPLICIT method which will require you to do a lot of coding to get a hierarchial Tree.
May be if you can explain more on the format you would like to get the data, can help.
Thanks.|||no really just an arraylist (working in vb) of the output (Top Level 1/Second Level 1 etc. etc. ) - trying to do it on sqlserver because the recursive code (even thought the maximum level is 5) is taking too long to run
|||Dylan,
Try the link below most things you want to do with Arrays in SQL is covered by Joe Celko, browse his book in the link at your local book store. Hope this helps.
http://www.onlamp.com/pub/a/onlamp/2004/08/05/hierarchical_sql.html|||okay some of that makes sense - doing this in SQL is really really new to me - the 'Searching For Superiors' section seems to cover what I need but his example uses a path string to show the path to the node - I dont have that I am using a child sub child method
oh this is so fun|||Joe Celko is an ANSI SQL purist so what you are looking for he did not cover because he thinks most things you do in the database is covered by ANSI SQL. This new link I have given you before he uses T-SQL and there is code files you can download to play with. BTW click on 2000 so you don't have to go through old code for earlier versions. Hope this helps
http://www.sommarskog.se/arrays-in-sql.html#iterative|||

You can always utilize recursion within self-joining tables. For example a function to "expand" the hierarchy (assuming the table name is CATEGORIES)

CREATE Function [dbo].[GetParent](@.currentid int,@.includestartnode bit)
returns @.results TABLE(id int,orderid int)
AS

BEGIN

DECLAre @.level int;
SET @.level=-1

IF @.includestartnode=1
BEGIN
INSERT INTO @.results
VALUES(@.currentid,0)
END

WHILE EXISTS (SELECT parentid FROM CATEGORIES WHEREid=@.currentid)
BEGIN

SELECT @.currentid=parentId
FROM CATEGORIES
WHEREid=@.currentid

IF @.@.ROWCOUNT=0
BEGIN
BREAK
END

INSERT INTO @.results(id,orderid)
SELECT @.currentid, @.level
FROM CATEGORIES
WHEREid=@.currentid

SET @.level=@.level - 1

END

RETURN

END

Using this with a call:

SELECT idFROM dbo.GetParent(3,1)

ORDERby orderid

would return
ID
===
1
3
As you can see it returns the ID list of the hierarchical nodes so it is rather generic function (returns nodes who have parent-child relationship) going with "bottom-to-up" principle. You could utilize this in getting the "path"
==
DECLARE @.pathnvarchar(1000)

SELECT @.path=COALESCE(@.path+'/','')+

+CAST(CategoryNameasnvarchar)

from CATEGORIES cINNERJOIN dbo.GetParent(3,1) tmp

ON c.id=tmp.id

ORDERBY orderid

select @.path

Returns

Top level 1/Second level 1

Note that of course, if you want, you can combine getting the data into the function or just on plain proc to do the job. E.g you can do the recutrsive stuff which I here do in function, also in stored proc if you want to.