Sql-server – Query to List all hierarchical children with parent, grandparent etc. from entire table

recursivesql server

I have a basic sql table with an ID and an optional parentID from the same table. This has multiple levels. I need to get a list of all IDs with each of its parents all the way to the top.

if object_id('tempdb..#table') is not null drop table #table
create table #table (id int identity(1,1), [Name] varchar(16), ParentID int null)
insert into #table
values
('John',null)
,('Doe',1)
,('Mike',1)
,('Neil',3)
,('Sylvia',4)
,('Mary',2)

+----+--------+----------+
| id |  Name  | ParentID |
+----+--------+----------+
|  1 | John   | NULL     |
|  2 | Doe    | 1        |
|  3 | Mike   | 1        |
|  4 | Neil   | 3        |
|  5 | Sylvia | 4        |
|  6 | Mary   | 2        |
+----+--------+----------+

I need to get a list like this:

ID  | Parent | DirectParent
_______________________
2     1       1
3     1       1
4     3       1
4     1       0
5     4       1
5     3       0
5     1       0
  • It ignores any record that does not have a parent.
  • For each child there is a record matching it to one of its parents
  • There is a bit column to know which parent is the one directly above it

How can I query the above table to get this data?

Edit: see what I have been working with

 WITH Recursives AS (
        SELECT   CAST(ID AS VARCHAR(MAX)) + '\' ID,*       
        FROM    table
        WHERE   id=@ID
        UNION ALL
        SELECT  r.ID + CAST(t.ID AS VARCHAR(MAX)) + '\', t.*
        FROM    table t 
        INNER JOIN Recursives r    ON  t.parentID= r.ID)

    SELECT ID,Name,Parent
    FROM Recursives r

I use this to get a list for a specific parent all the way down.

  • I can't figure out how to do this for the entire table without
    getting an error
    The statement terminated. The maximum
    recursion 100 has been exhausted before statement completion.
  • How can I get the same child multiple times. Once with each parent?

Best Answer

you just cannot do recursive on millions of rows.you have to put some filter

Try this,

F object_id('tempdb..#table') IS NOT NULL
    DROP TABLE #table

CREATE TABLE #table (
    id INT identity(1, 1)
    ,[Name] VARCHAR(16)
    ,ParentID INT NULL
    )

INSERT INTO #table
VALUES('John',null)
,('Doe',1)
,('Mike',1)
,('Neil',3)
,('Sylvia',4)
,('Mary',2)

    WITH CTE
AS (
    SELECT id
        ,ParentID,1 DirectP
    FROM #table

    UNION ALL

    SELECT r.ID
        ,t.parentID,0 DirectP
    FROM #table t
    INNER JOIN CTE r ON t.id = r.parentID
    WHERE t.ParentID IS NOT NULL
    )
SELECT r.ID
    ,r.ParentID
   ,DirectP
FROM CTE r

ORDER BY id
    ,ParentID DESC