Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 452
CesarGon Avatar asked Nov 18 '09 16:11

CesarGon


People also ask

How do you write a recursive query in SQL?

Recursion is achieved by WITH statement, in SQL jargon called Common Table Expression (CTE). It allows to name the result and reference it within other queries sometime later. Naming the result and referencing it within other queries.

How recursive query works in SQL Server?

Recursion occurs because of the query referencing the CTE itself based on the Employee in the Managers CTE as input. The join then returns the employees who have their managers as the previous record returned by the recursive query. The recursive query is repeated until it returns an empty result set.

What is CTE and recursive CTE in SQL Server?

A Recursive CTE is a CTE that references itself. The CTE repeatedly executes, returns subsets of data, until it returns the complete result set. Syntax. WITH cte_name AS ( cte_query_definition (or) initial query -- Anchor member UNION ALL recursive_query with condition -- Recursive member ) SELECT * FROM cte_name.

What is recursive subquery in SQL?

A recursive subquery factoring clause must contain two query blocks combined by a UNION ALL set operator. The first block is known as the anchor member, which can not reference the query name. It can be made up of one or more query blocks combined by the UNION ALL , UNION , INTERSECT or MINUS set operators.


1 Answers

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
like image 195
Damir Sudarevic Avatar answered Nov 10 '22 05:11

Damir Sudarevic