Recursive same-table query in SQL Server 2008
I have the following table in a SQL Server 2008 database:
Id Name ParentFolder
-- ---- ------------
1 Europe NULL
2 Asia NULL
3 Germany 1
4 UK 1
5 China 2
6 India 2
7 Scotland 4
ParentFolder is a FK to Id in the same table. I would like to create a view that results in something like this:
Id Name FullName
-- ---- --------
1 Europe Europe
2 Asia Asia
3 Germany Europe/Germany
4 UK Europe/UK
5 China Asia/China
6 India Asia/India
7 Scotland Europe/UK/Scotland
As you can see, I need to build the FullName values by recursively using the ParentFolder relationship an arbitrary number of times until a NULL is found.
Edit. Each row in the table "knows" what other row is its parent, but does not know its absolute position in the hierarchy. For this reason, a lineage system where each row stores its absolute location in the hierarchy tree would not be appropriate.
I am aware of the hierarchyid feature of SQL Server 2008 but, as far as I know, it only works with a fixed number of recursion levels. In my case, however, you never know how many levels you will find, and they may change from row to row.
I have also seen similar questions to this posted here. However, I think that nobody asked about building "paths" for each row in a table. Sorry if I missed it.
Many thanks.
Solution 1:
Try this one:
DECLARE @tbl TABLE (
Id INT
,[Name] VARCHAR(20)
,ParentId INT
)
INSERT INTO @tbl( Id, Name, ParentId )
VALUES
(1, 'Europe', NULL)
,(2, 'Asia', NULL)
,(3, 'Germany', 1)
,(4, 'UK', 1)
,(5, 'China', 2)
,(6, 'India', 2)
,(7, 'Scotland', 4)
,(8, 'Edinburgh', 7)
,(9, 'Leith', 8)
;
WITH abcd
AS (
-- anchor
SELECT id, [Name], ParentID,
CAST(([Name]) AS VARCHAR(1000)) AS "Path"
FROM @tbl
WHERE ParentId IS NULL
UNION ALL
--recursive member
SELECT t.id, t.[Name], t.ParentID,
CAST((a.path + '/' + t.Name) AS VARCHAR(1000)) AS "Path"
FROM @tbl AS t
JOIN abcd AS a
ON t.ParentId = a.id
)
SELECT * FROM abcd
Solution 2:
I'm not sure if this will work in your case, but in this example http://www.pure-performance.com/2009/03/managing-hierarchical-data-in-sql/ there is something about using an extra column, called lineage.
I have used this method with success.
Solution 3:
Sounds like you should checkout CLR support for Sql Sever.
CLR integration means that you can now write stored procedures, triggers, user-defined types, user-defined functions (scalar and table-valued), and user-defined aggregate functions using any .NET Framework language, including Microsoft Visual Basic .NET and Microsoft Visual C#.