CODEWITHSUNDEEP
sql-serversql-server-2008common-table-expressionrecursive-query
Paul Fleming
Paul Fleming

Reputation: 24526

CTE to return all items in hierarchy

I have a table with a recursive hierarchy (i.e. ID, ParentID). For any item in this hierachy, I want to be able to bring back a list of everything UP AND DOWN the hierarchy along with the level for each row. Assume that a parent can only ever have a single child.

Take for example the following:

ID    ParentID
--------------
1     NULL
2     1
3     2
4     NULL
5     4
6     5

Given ID 1, 2, or 3, I want to return:

ID    ParentID    Level
-----------------------
1     NULL        1
2     1           2
3     2           3

I've done this before, but I can't remember how. I know the solution involves a CTE, I just can't get it right! Any help is appreciated.

Upvotes: 2

Views: 3256

Answers (3)

Hutch
Hutch

Reputation: 1007

The most barebones version of the CTE query I could come up with is:

WITH Ancestry (AncestorID, DescendantID)
AS
(
    SELECT 
        ParentID, ID
    FROM
        dbo.Location
    WHERE
        ParentID IS NOT NULL
UNION ALL
    SELECT 
        P.AncestorID, C.ID
    FROM
        dbo.Location C
    JOIN
        Ancestry P on C.ParentID = P.DescendantID
)
SELECT * FROM Ancestry

The result is a list of all Ancestor/Descendant relationships that exist in the table.

The final "SELECT * FROM Ancestry" can be replaced with something more complex to filter, order, etc.

To include reflexive relationships, the query can be modified by adding two lines to the final SELECT statement:

SELECT * FROM Ancestry
UNION
SELECT ID, ID FROM dbo.Location

Upvotes: 0

bhupendra singh
bhupendra singh

Reputation: 127

;WITH Recursive_CTE AS (
     SELECT
          child.ExecutiveId,
          CAST(child.ExecutiveName as varchar(100)) BusinessUnit,
          CAST(NULL as bigint) ParentUnitID,
          CAST(NULL as varchar(100)) ParentUnit,
          CAST('' as varchar(100)) LVL,
          CAST(child.ExecutiveId as varchar(100)) Hierarchy,
      1 AS RecursionLevel
     FROM Sales_Executive_level child
     WHERE ExecutiveId = 4000 --your Id which you want to get all parent node
     UNION ALL 
     SELECT
      child.ExecutiveId,
      CAST(LVL + child.ExecutiveName as varchar(100)) AS BusinessUnit,
      child.ParentExecutiveID,
      parent.BusinessUnit ParentUnit,
      CAST('' + LVL as varchar(100)) AS LVL,
      CAST(Hierarchy + ':' + CAST(child.ExecutiveId as varchar(100)) as varchar(100)) Hierarchy,
      RecursionLevel + 1 AS RecursionLevel
     FROM Recursive_CTE parent 
     INNER JOIN Sales_Executive_level child ON child.ParentExecutiveID = parent.ExecutiveId                           
    )
    SELECT * FROM Recursive_CTE ORDER BY Hierarchy  
    OPTION (MAXRECURSION 300);

Upvotes: -1

podiluska
podiluska

Reputation: 51514

;with cte as 
(
    select *, 1 as level from @t where id = @yourid
    union all
    select t.*, level - 1
    from cte 
        inner join @t t on cte.parent = t.id
),
cte2 as
(   
    select * from cte
    union all
    select t.*, level+1
    from cte2 
        inner join @t t on cte2.id = t.parent

)
    select id,parent, ROW_NUMBER() over (order by level) level
    from (  select distinct id, parent, level from cte2) v

Upvotes: 6

Related Questions