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