In SQL Server, how can I select all records in a recursive table?

Question

I have a recursive table in which each record has an identifier and PARENTID. PARENTID points to another identifier in the same table. Is there a way in SQL Server to select a complete "tree" in a single expression? I could write a recursive function to go from the parent to all the children, but I would like to do this in a single request.

In Oracle, it will look like this:

select
  id,
  parentid,
  nodename
from 
  MY_SCHEMA.MY_TABLE
  connect by nocycle prior parentid = id
start with id = :starting_id_number
order by
  id 

What is the equivalent of SQL Server?

Answer 1

Here is an example that I have compiled for you. It demonstrates the use of a recursive common table expression (CTE).

CREATE TABLE #tempTable
(
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    parentID INT NULL
)

INSERT INTO #tempTable (parentID) VALUES (null)
INSERT INTO #tempTable (parentID) VALUES (1)
INSERT INTO #tempTable (parentID) VALUES (1)
INSERT INTO #tempTable (parentID) VALUES (2)
INSERT INTO #tempTable (parentID) VALUES (3)
INSERT INTO #tempTable (parentID) VALUES (2)
INSERT INTO #tempTable (parentID) VALUES (5)


SELECT * FROM #tempTable;

WITH RecursiveTable (ID, ParentID, Level)
AS
(
    --Anchor
    SELECT  tt.ID, 
            tt.ParentID, 
            0 AS Level
    FROM #tempTable AS tt
    WHERE parentID IS null
    UNION ALL
    --Recursive member definition
    SELECT  tt.ID, 
            tt.ParentID, 
            LEVEL + 1
    FROM #tempTable AS tt
        INNER JOIN RecursiveTable rt ON
        tt.ParentID = rt.ID
)
SELECT * 
FROM RecursiveTable

DROP TABLE #tempTable

: SQL Server 2008 , , . .

http://technet.microsoft.com/en-us/library/bb677213.aspx

Answer 2

CTE :

CREATE TABLE TestTable
( 
    ID int primary key NOT NULL,
    ParentID int
)

INSERT INTO TestTable VALUES (0, null)
INSERT INTO TestTable VALUES (1, 0)
INSERT INTO TestTable VALUES (2, 0)
INSERT INTO TestTable VALUES (3, 1)
INSERT INTO TestTable VALUES (4, 3)


-- Get branch
;WITH TreeRecCTE (ID, ParentID, IDPath)
AS
(
   SELECT ID, ParentID, CONVERT(varchar(max), ID) As IDPath
      FROM TestTable
      WHERE ParentID IS NULL
   UNION ALL
   SELECT
            Child.ID,
            Child.ParentID,
            Parent.IDPath + '.' + CONVERT(varchar(100),Child.ID) As IDPath
        FROM TestTable As Child INNER JOIN TreeRecCTE AS Parent ON Child.ParentID = Parent.ID
  )
SELECT * FROM TreeRecCTE WHERE IDPath LIKE '%.1.%' ORDER BY ParentID ASC 


-- Get complete tree:
;WITH TreeRecCTE (ID, ParentID, IDPath)
AS
(
   SELECT ID, ParentID, CONVERT(varchar(max), ID) As IDPath
      FROM TestTable
      WHERE ParentID IS NULL
   UNION ALL
   SELECT
            Child.ID,
            Child.ParentID,
            Parent.IDPath + '.' + CONVERT(varchar(100),Child.ID) As IDPath
        FROM TestTable As Child INNER JOIN TreeRecCTE AS Parent ON Child.ParentID = Parent.ID
  )
SELECT * FROM TreeRecCTE ORDER BY ParentID ASC 

Answer 3

id , . : "tree_id", .

treeid|id|parentid|nodename
1     |1 | null   | rootOfTreeOne
1     |2 | 1      | childOfRootOne
1     |3 | 1      | secondChild
2     |4 | null   | rootOfSecondTree
2     |5 | 4      | childofSecondTree

. , select.

select * from tree_table where tree_id = 1;

, .

, .

Answer 4

, , .

( ), , .

, , .

, MPTT , . , , /, , , .

, , MPTT.

Answer 5

You can use Common Table Expressions to execute a recursive query. Do a Google search on "Recursive queries using common table expressions", there is an msdn article.

sigh new users cannot add hyperlinks.

Answer 6

See also this other tree navigation in SQL Server question