advertisement
Premier Club Log In/Registration
  Include Code  Search Tips
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   SKILLBUILDING  |   TIP BANK  |   SOURCEBANK  |   FORUMS  |   NEWSLETTERS
Browse DevX
Partners & Affiliates
advertisement
advertisement
Tip of the Day
Average Rating: 3.3/5 | Rate this item | 7 users have rated this item.
Expertise: Intermediate
Language: SQL
October 5, 2004
Hierarchical Tree Structures in Database Tables
This recursive function retrieves a hierarchical tree structure from a table with just four columns that describe each node in the tree: Level (the depth of the node in the tree), ID, Name, and ParentID. Given an ID and a Level, the function opens a cursor and retrieves the entire branch of the tree for the specified node at that level.

First, create the table:


Create Table Tree(ID int,Name Varchar(10),ParentID int)
Go
Next, create the function:

Create Function getTree(@ID Int,@Level int=1)
Returns @Tree Table(Level int,ID int,Name Varchar(10),ParentID int)
As
Begin
	Declare @tID int,@tName Varchar(10),@tParentID int
	Declare TreeCur Cursor
	For	Select * From Tree Where ParentID=@ID
	Open TreeCur
	FETCH NEXT FROM TreeCur into @tID,@tName,@tParentID
	Insert Into @Tree Select @Level,* From Tree Where ID=@ID
	Set @Level=@Level+1
	WHILE (@@fetch_status = 0)
	Begin
		Insert Into @Tree Select * From dbo.getTree(@tID,@Level)
		FETCH NEXT FROM TreeCur into @tID,@tName,@tParentID
	End
	Close TreeCur
	Deallocate TreeCur
	return
End
Lastly, fill the table:

INSERT INTO Tree  VALUES(1,'Root',0)
Go
INSERT INTO Tree  VALUES(2,'Node 1',1)
Go
INSERT INTO Tree  VALUES(3,'Node 2',1)
Go
INSERT INTO Tree  VALUES(4,'Node 1.1',2)
Go
INSERT INTO Tree  VALUES(5,'Node 1.2',2)
Go
INSERT INTO Tree  VALUES(6,'Node 1.1.1',4)
Go
INSERT INTO Tree  VALUES(7,'Node 2.1',3)
Go
Build the tree using Function
To get the tree built from the root, use this:

select * from getTree(1,DEFAULT)

Level       ID          Name       ParentID    
----------- ----------- ---------- ----------- 
1           1           Root       0
2           2           Node 1     1
3           4           Node 1.1   2
4           6           Node 1.1.1 4
3           5           Node 1.2   2
2           3           Node 2     1
3           7           Node 2.1   3

(7 row(s) affected)
To retrieve the tree built from Node 1, use:

select * from getTree(2,DEFAULT)

Level       ID          Name       ParentID    
----------- ----------- ---------- ----------- 
1           2           Node 1     1
2           4           Node 1.1   2
3           6           Node 1.1.1 4
2           5           Node 1.2   2

(4 row(s) affected)
To retrieve the tree built from Node 2, use:

select * from getTree(3,DEFAULT)

Level       ID          Name       ParentID    
----------- ----------- ---------- ----------- 
1           3           Node 2     1
2           7           Node 2.1   3

(2 row(s) affected)
Rohit K.
If you have a hot tip and we publish it, we'll pay you. However, due to accounting overhead we no longer pay $10 for a single tip submission. You must accumulate 10 acceptable tips to receive payment. Be sure to include a clear explanation of what the technique does and why it's useful. If it includes code, limit it to 20 lines if possible. Submit your tip here.
Please rate this item (5=best)
 1  2  3  4  5
advertisement
advertisement
Advertising Info  |   Member Services  |   Permissions  |   Contact Us  |   Help  |   Feedback  |   Site Map  |   Network Map  |   About

internet.commediabistro.comJusttechjobs.comGraphics.com

Search:

WebMediaBrands Corporate Info

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | Shopping | E-mail Offers | Freelance Jobs