Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.

Tip of the Day
Language: SQL
Expertise: Intermediate
Oct 5, 2004



Application Security Testing: An Integral Part of DevOps

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)
Next, create the function:

Create Function getTree(@ID Int,@Level int=1)
Returns @Tree Table(Level int,ID int,Name Varchar(10),ParentID int)
	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)
		Insert Into @Tree Select * From dbo.getTree(@tID,@Level)
		FETCH NEXT FROM TreeCur into @tID,@tName,@tParentID
	Close TreeCur
	Deallocate TreeCur
Lastly, fill the table:

INSERT INTO Tree  VALUES(1,'Root',0)
INSERT INTO Tree  VALUES(2,'Node 1',1)
INSERT INTO Tree  VALUES(3,'Node 2',1)
INSERT INTO Tree  VALUES(4,'Node 1.1',2)
INSERT INTO Tree  VALUES(5,'Node 1.2',2)
INSERT INTO Tree  VALUES(6,'Node 1.1.1',4)
INSERT INTO Tree  VALUES(7,'Node 2.1',3)
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. Gupta
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.
Thanks for your registration, follow us on our social networks to keep up-to-date