Build a path from parent child relationship

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 2008 Forums

Transact-SQL (2008)

Build a path from parent child relationship

Author

Topic

figmo
Starting Member

18 Posts

Posted - 2012-05-31 : 14:29:25

Wow - this is turning out to be WAY more complicated than I thought.

Have this table:
Id (PK, int, not null)
Name (nvarchar(max), not null)
ParentId (int, not null)

Need a query that returns a string like
"NameRoot->NameChild1->NameChild2"
(Sort of like a path in a file system)

I do not know in advance how deep the children might be. Might just be a Root. Might be 10 children deep.

Is there a way to do this from a single SQL statement/statements? Or do I need to write a stored procedure?

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-31 : 15:05:31

Please provide some sample data of your table and output u required..

Vijay is here to learn something from you guys.

SQL Padawan
Starting Member

4 Posts

Posted - 2012-05-31 : 15:44:46

Still trying to wrap my head around recursion but here you go...

;WITH sampledata
AS (
SELECT id = 1,name = 'james',parentid = 0
UNION ALL
SELECT 2,'money',1
UNION ALL
SELECT 3,'cash',2
UNION ALL
SELECT 4,'benjamin',0
UNION ALL
SELECT 5,'franklin',4
)
,rcte
AS (
SELECT id,name,lvl = 0,pth = CAST(name AS VARCHAR(MAX))
FROM sampledata
WHERE parentid = 0
UNION ALL
SELECT s.id,r.name,lvl+1,CAST(pth + '->' + s.name AS VARCHAR(MAX))
FROM rcte r
INNER JOIN sampledata s ON s.parentid = r.id
WHERE CHARINDEX(s.name,pth) = 0
)
SELECT lvl,pth FROM rcte OPTION(MAXRECURSION 2000)--0 = max recursion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-31 : 15:45:41

you need to use recursive CTE for this


;With Hierarchy (ID,Name,ParentID,Path)
AS
(
SELECT t.Id,t.Name,t.ParentId, CAST(t.Name AS varchar(max))
FROM Table t
LEFT JOIN Table t1
ON t1.Id = t.ParentId 
WHERE t1.Id IS NULL
UNION ALL
SELECT t.Id,t.Name,t.ParentId, CAST(h.Path + '->' + t.Name AS varchar(max))
FROM Hierarchy h
INNER JOIN Table t
ON t.ParentId = h.Id
)

SELECT Name,Path
FROM Hierarchy
OPTION (MAXRECURSION 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources