Is it possible to create a "tree resolver" in SQL?
I have a table:
ID Name Parent
1 a
2 b 1
3 c 1
4 d 3
Now I want a SQL query that returns:
ID PATH
1 /a
2 /a/b
3 /a/c
4 /a/c/d
Is this possible with SQL? It would make many things easier for me. Any help would really be appreciated!
Using CTE in sql server 2005 and later, here's a snippet that I have to do this:
WITH Paths([Level], [FullPath], [ID]) AS
(
SELECT
0 AS [Level],
Name AS FullPath,
ID
FROM dbo.Entity
WHERE (ParentEntityID IS NULL)
UNION ALL
SELECT
p.[Level] + 1 AS [Level],
CASE RIGHT(p.[FullPath], 1)
WHEN '\' THEN p.[FullPath] + c.[Name]
ELSE p.[FullPath] + '\' + c.[Name]
END AS FullPath,
c.ID
FROM dbo.Entity AS c
INNER JOIN Paths AS p ON p.ID = c.ParentEntityID
)
SELECT [FullPath], [ID]
FROM Paths
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With