Building a tree - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Building a tree

Author

Topic

brooks
Starting Member

3 Posts

Posted - 2004-02-03 : 10:30:31

I'm building a custom module for an existing application. The application has a virtual folder structure that exists only in the database. The basic table structure is as follows:

WS_Folders
----------
FolderName varchar(50)
FolderID int
ParentID int

I need to retrieve in this format:

"Root > Parent > Parent > Child"

Where "Parent" and "Child" are just the folder names.

There is a root folder where the folderid = 0

Any help?

Thanks!

Brooks Williams

brooks
Starting Member

3 Posts

Posted - 2004-02-03 : 17:30:26

Well, I figured it out on my own. If anyone can think of a better way, let me know:

First I created a function that would return the "Folder>Folder>Folder" format I needed for each folder


CREATE FUNCTION dbo.GetPath (@folderid int) 
RETURNS nvarchar(2000)

AS
BEGIN

DECLARE @Control bit
DECLARE @ParentID int
DECLARE @Path nvarchar(2000)

SET @Path = ''
SET @ParentID = @folderid
SET @Control = 0

WHILE @Control = 0 
BEGIN 

	SELECT @Path = FolderName + '>' + @Path, @ParentID = ParentID
	FROM WS_Folders 
	WHERE FolderID = @folderid

	SET @folderid = @ParentID

	IF @folderid = 0
	BEGIN	
		SET @Control = 1
	END
END 

-- remove trailing '>'
RETURN LEFT(@Path, LEN(@Path) - 1)

END

Then a simple query to return the results for the project:


SELECT A.FolderName, A.FolderID 
FROM 
   (SELECT dbo.GetPath(FolderID) AS FolderName, FolderID 
    FROM WS_Folders 
    WHERE ProjectID = 180) AS A
ORDER BY A.FolderName

And the output looks like this:


Project Documents   7963
Project Documents>Documents   7940
Project Documents>Documents>Architecture and Engineering 7978

etc...

Like I said, any ideas as to how this can be improved would be greatly appreciated.

Brooks Williams

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-02-04 : 08:56:49

Look up Trees and Hierarchies on this site it may help you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources