Pages

Friday, August 7, 2009

Get Branch and Complete Tree in SQL Server

This post is useful for developers who want to build a tree as per parent id, And a programmer who want a all child node from parent id. and All tree structure in SQL Server.

Using this code u can develop a tree structure in SQL Server.

CREATE TABLE
TestTable ( ID int primary key NOT NULL, CatName varchar(100), ParentID int )
INSERT INTO TestTable VALUES (0,NULL,null)
INSERT INTO TestTable VALUES (1,'A', 0)
INSERT INTO TestTable VALUES (2,'B', 0)
INSERT INTO TestTable VALUES (3,'A1', 1)
INSERT INTO TestTable VALUES (4,'B1', 2)
INSERT INTO
TestTable VALUES (5,'A11',3)
INSERT INTO TestTable VALUES (6,'A12',3)

-- Get branch

WITH TreeRecCTE AS
(
SELECT ID, CatName, ParentID, CONVERT(varchar(MAX), ID) AS IDPath
FROM TestTable
WHERE (ParentID IS NULL)
UNION ALL
SELECT Child.ID, Child.CatName, Child.ParentID,
Parent.IDPath + ',' + CONVERT(varchar(100), Child.ID) AS IDPath
FROM TestTable AS Child INNER JOIN TreeRecCTE AS Parent ON Child.ParentID = Parent.ID
)

SELECT ID, CatName, ParentID, IDPath
FROM TreeRecCTE AS TreeRecCTE_1
WHERE (IDPath LIKE '%,1,%')
ORDER BY ParentID

-- Get complete tree:

WITH TreeRecCTE AS
(
SELECT ID, CatName, ParentID, CONVERT(varchar(MAX), ID) AS IDPath
FROM TestTable
WHERE (ParentID IS NULL)
UNION ALL
SELECT Child.ID, Child.CatName, Child.ParentID,
Parent.IDPath + ',' + CONVERT(varchar(100), Child.ID) AS IDPath
FROM TestTable AS Child INNER JOIN TreeRecCTE AS Parent ON Child.ParentID = Parent.ID
)

SELECT ID, CatName, ParentID, IDPath
FROM TreeRecCTE AS TreeRecCTE_1
ORDER BY ParentID

4 comments:

Sandeep Ramani said...

nice article parag !!

bca said...

Hi
Sir
Good Morning

I am DBA from Rajkot

This is nice and needful blogs.

This is good job.

Nandip Makwana said...

IDPath column tweak is great . . .

Nandip Makwana said...

Great tweak about IDPath . . . how to get total number of child record for each level?

Post a Comment