Recursive same-table query in SQL Server 2008

Question

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.

Damir Sudarevic · Accepted Answer · 2009-11-18T20:33:21.520

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

score 2 · Answer 2 · answered Nov 18 '09 at 16:50

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.

answered Nov 18 '09 at 16:50

John Boker

82,559
17
97
130

Thanks, John. I am not sure this would work, since the Lineage column means that every row in the table must "know" its absolute position in the hierarchy. I didn't say it in my original post (editing it now to clarify), but I would rather avoid using this kind of absolute positioning system. :-) – CesarGon Nov 18 '09 at 16:59

score 2 · Answer 3 · answered Nov 18 '09 at 17:14

2

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#.

answered Nov 18 '09 at 17:14

Chad

892
2
11
25

Thanks Chad. So, basically, you are saying that I need to create a table-valued function in C# (or VB.NET) and use it to compute the FullName column in the view, right? – CesarGon Nov 18 '09 at 17:44
@CesarGon: Yes. I think the solution will be easier to write in c# as a CLR SP or Function than the gooble of TSQL i came up for this. I also think this will be more efficient than TSQL(because of LOCKS/LATCHES associated with cursors). good luck – Chad Nov 18 '09 at 19:37
1

You really don't need to write a CLR function, look at Damir Sudarevic's answer below. "Common Table Expressions" solved most of the hierarchical data issues. http://msdn.microsoft.com/en-us/library/ms186243.aspx – Mehmet Ergut Nov 18 '09 at 21:18

score 1 · Answer 4 · answered Jan 29 '13 at 20:53

I tried the solution above, but found that this only worked for me to 2 levels. (Perhaps I have not understood or missed something.)

In order to get the fully qualified path for m solution I have succeeded with this custom function:

CREATE FUNCTION GetFQN(@recid int)
RETURNS VARCHAR(1000)

AS

BEGIN
    DECLARE @path AS VARCHAR(1000)
    DECLARE @parent_recid AS INT

    SET @path           =   (SELECT BranchName FROM Branches WHERE Recid = @recid)
    SET @parent_recid   =   (SELECT recid_parent FROM Branches WHERE Recid = @recid)


    WHILE @parent_recid != -1
    BEGIN
        SET @path = (SELECT BranchName FROM Branches WHERE recid = @parent_recid) + '/' + @path 
        SET @parent_recid = (SELECT recid_parent FROM Branches WHERE recid = @parent_recid)
    END

    RETURN (@Path)
END

Recursive same-table query in SQL Server 2008

4 Answers4

Linked