SQL: Get Parent Tree Hierarchy from child

Question

I have a table with Id, ParentId, Tree, TopParentId. Here's an example structure:

           0
       ___/ \___
      /         \
    1            4
   / \          / \
  2   7        5   8
 /            /
3            6

Input Table:

Id     ParentId      Tree                     TopParentId
---    ----------    --------------------     --------
 1     0              NULL                    NULL
 2     1              NULL                    NULL
 7     1              NULL                    NULL
 3     2              NULL                    NULL
 4     0              NULL                    NULL
 5     4              NULL                    NULL
 6     5              NULL                    NULL
 8     4              NULL                    NULL

This is the output I am looking for when I pass Id = 3

OUTPUT:

Id     ParentId      Tree                     TopParentId
---    ----------    --------------------     --------
 1     2              3 > 2 > 1 > 0           0

The CTE query should be able to handle multiple ids like 3,7

OUTPUT:

Id     ParentId      Tree                     TopParentId
---    ----------    --------------------     --------
 3     2              3 > 2 > 1 > 0           0
 7     1              7 > 1 > 0               0

The end goal is to take the Tree and TopParentId columns and update the corresponding Id

Here's the query I have tried till now:

WITH CTE AS (
      SELECT Id, ParentId,0 AS [Level], CAST(Id AS varchar(1000)) AS Heirarchy,Id AS TopParentId
      FROM dbo.table
      WHERE Id IN (SELECT Id FROM table WHERE ParentId IS NULL)
      UNION ALL

      SELECT mgr.Id, mgr.ParentId, TASKCTE.[Level] +1 AS [Level], 
            CAST(( CAST(mgr.Id AS VARCHAR(1000)) + '>' + CTE.Heirarchy) AS varchar(1000)) AS Heirarchy, CTE.TopParentId
      FROM CTE
        INNER JOIN dbo.table AS mgr
          ON CTE.Id = mgr.ParentId
    )
    UPDATE t SET t.[LEVEL] = TC.[LEVEL], t.ParentTree = TC.Heirarchy, t.TopParentId = TC.TopParentId
    FROM dbo.table AS t
    JOIN (SELECT * FROM CTE WHERE Id IN(SELECT DISTINCT Id FROM INSERTED) AND ParentId IS NOT NULL) TC
    ON 
    t.Id = TC.Id 

The above query works but its CPU/RAM intensive as it starts from Parent. I need the CTE to start from the Child but the Tree needs to be exactly same as the example outputs.

Answer 1

This appears to work:

drop table if exists #t;
with cte as (
    select * from (values
        (1, 0),
        (2, 1),
        (7, 1),
        (3, 2),
        (4, 0),
        (5, 4),
        (6, 5),
        (8, 4)
    )
    as x(ID, ParentID)
)

select *
into #t
from cte;

with cte as (
    select * ,
        ID as [start] ,
        ParentID as [FirstParent] ,
        1 as [level] , 
        cast(ID as varchar(max)) as Tree
    from #t
    where ID in (3, 7)

    union all

    select p.*,
        c.[start] ,
        c.FirstParent ,
        c.[level] + 1 ,
        cast(concat(c.Tree, ' > ', p.ID) as varchar(max))
    from cte as c
    join #t as p
        on c.ParentID = p.ID
), top_level as (
    select *, row_number() over (partition by [start] order by [level] desc) as rn
    from cte
)
select [start] as ID , 
    FirstParent as ParentID , 
    concat(Tree, ' > ', ParentID) ,
    ParentID as TopParentID
from top_level
where rn = 1;

By way of exposition, the first part just creates your test data (pro-tip: if you do this, people are more likely to be able to help since you've lowered the friction to do so!). The meat of the solution just uses the desired IDs as the "base case" for the recursion and the recursive step says "take the previous level's ParentID to find the next level's ID". The rest is just to keep track of the starting and ending point.

Answer 2

You simply need to create procedure to get the desired result.

Try this or variant of this will help.

Create proc CalculateLevel
( @passid nvarchar(max) )
As
Begin
declare @query nvarchar(max)
set @query = '
WITH CTE AS (
      SELECT Id, ParentId,0 AS [Level], CAST(Id AS varchar(1000)) AS Heirarchy,Id AS TopParentId
      FROM dbo.tab
      WHERE Id IN (SELECT Id FROM tab WHERE ParentId IS NULL)
      UNION ALL

      SELECT mgr.Id, mgr.ParentId, CTE.[Level] +1 AS [Level], 
            CAST(( CAST(mgr.Id AS VARCHAR(1000)) + ''>'' + CTE.Heirarchy) AS varchar(1000)) AS Heirarchy, CTE.TopParentId
      FROM CTE
        INNER JOIN dbo.tab AS mgr
          ON CTE.Id = mgr.ParentId
    )

    UPDATE t SET t.[LEVEL] = TC.[LEVEL], t.ParentTree = TC.Heirarchy, t.TopParentId = TC.TopParentId
    FROM dbo.tab AS t
    JOIN (SELECT * FROM CTE WHERE  ParentId IS NOT NULL) TC
    ON 
    t.Id = TC.Id  where t.id in ( ' + @passid  + ')'

    print @query

  exec sp_executesql @query
End