Branch of 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 2005 Forums

Transact-SQL (2005)

Branch of a Tree

Author

Topic

VentureFree
Starting Member

19 Posts

Posted - 2008-09-15 : 08:45:42

I've got a table that creates a tree structure using an ID column and a ParentID column.

CREATE TABLE MyTree (ID int, ParentID int, Name varchar)
INSERT INTO MyTree VALUES (1, 0, 'Root1')
INSERT INTO MyTree VALUES (2, 0, 'Root2')
INSERT INTO MyTree VALUES (4, 1, 'Branch1')
INSERT INTO MyTree VALUES (5, 1, 'Branch2')
INSERT INTO MyTree VALUES (6, 2, 'Branch3')
INSERT INTO MyTree VALUES (7, 4, 'Sub1Branch1')
INSERT INTO MyTree VALUES (8, 4, 'Sub2Branch1')
INSERT INTO MyTree VALUES (9, 5, 'Sub1Branch2')
INSERT INTO MyTree VALUES (10, 5, 'Sub2Branch2')
INSERT INTO MyTree VALUES (11, 6, 'Sub1Branch3')
INSERT INTO MyTree VALUES (12, 6, 'Sub2Branch3')

The above creates this tree:
Root1
--Branch1
----Sub1Branch1
----Sub2Branch1
--Branch2
----Sub1Branch2
----Sub2Branch2
Root2
--Branch3
----Sub1Branch3
----Sub2Branch3

I'd like to select only those branches that belong to a specific root? For instance, given ID = 2, the select statement would give me items 6, 11, and 12. I can do it in a program, but is it possible to do that with only a sql statement?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-15 : 08:47:44

Yes. Use recursive Common Table Expression.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-15 : 08:53:59

[code]DECLARE @Sample TABLE
(
ID INT,
ParentID INT,
Name VARCHAR(20)
)

INSERT @Sample
SELECT 1, 0, 'Root1' UNION ALL
SELECT 2, 0, 'Root2' UNION ALL
SELECT 4, 1, 'Branch1' UNION ALL
SELECT 5, 1, 'Branch2' UNION ALL
SELECT 6, 2, 'Branch3' UNION ALL
SELECT 7, 4, 'Sub1Branch1' UNION ALL
SELECT 8, 4, 'Sub2Branch1' UNION ALL
SELECT 9, 5, 'Sub1Branch2' UNION ALL
SELECT 10, 5, 'Sub2Branch2' UNION ALL
SELECT 11, 6, 'Sub1Branch3' UNION ALL
SELECT 12, 6, 'Sub2Branch3'

;WITH Yak (ID, ParentID, [Name], [Path], Indent)
AS (
SELECT ID,
ParentID,
Name,
'/' + CAST(ID AS VARCHAR(MAX)),
0
FROM @Sample
WHERE ID = 2

UNION ALL

SELECT s.ID,
s.ParentID,
s.Name,
y.Path + '/' + CAST(s.ID AS VARCHAR(MAX)),
y.Indent + 1
FROM Yak AS y
INNER JOIN @Sample AS s ON s.ParentID = y.ID
)

SELECT Indent,
ID,
[Name],
ParentID
FROM Yak
ORDER BY [Path][/code]

E 12°55'05.63"
N 56°04'39.26"

VentureFree
Starting Member

19 Posts

Posted - 2008-09-15 : 09:24:02

quote:
Originally posted by Peso

Yes. Use recursive Common Table Expression.

Thank you. That is precisely what I needed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources