How do you get all ancestors of a node using SQL Server 2008 hierarchyid?

Question

Given a table with a hierarchyid type column, how do you write a query to return all rows that are ancestors of a specific node?

There is an IsDescendantOf() function, which is perfect for getting the children, but there's no corresponding IsAncestorOf() function to return ancestors (and the absence of a GetAncestors() function seems like quite an oversight.)

Answer 1

The most commonly used approach would be a recursive Common Table Expression (CTE)

WITH Ancestors(Id, [Name], AncestorId) AS (       SELECT             Id, [Name], Id.GetAncestor(1)       FROM             dbo.HierarchyTable       WHERE             Name = 'Joe Blow'  -- or whatever you need to select that node        UNION ALL        SELECT             ht.Id, ht.[Name], ht.Id.GetAncestor(1)       FROM             dbo.HierarchyTable ht       INNER JOIN              Ancestors a ON ht.Id = a.AncestorId ) SELECT *, Id.ToString() FROM Ancestors 

(adapted from a Simon Ince blog post)

Simon Ince also proposes a second approach where he just basically reverses the condition - instead of detecting those person entries that are an ancestor of the target person, he turns the check around:

DECLARE @person hierarchyid  SELECT @person = Id FROM dbo.HierachyTable WHERE [Name] = 'Joe Blow';  SELECT     Id, Id.ToString() AS [Path],      Id.GetLevel() AS [Level],     Id.GetAncestor(1),     Name FROM      dbo.HierarchyTable WHERE      @person.IsDescendantOf(Id) = 1 

This will select all the rows from your table, where the target person you're interested in is a descendant of - any level down the hierarchy. So this will find that target person's immediate and non-immediate ancestors all the way up to the root.

Answer 2

Here's an answer rolled into a single select:

SELECT t1.Id.ToString() as Path, t1.Name     FROM (SELECT * FROM HierarchyTable         WHERE Name = 'Joe Blow') t2,     HierarchyTable t1     WHERE t2.Id.IsDescendantOf(t1.Id) = 1 

Answer 3

Declare @hid hierarchyid=0x5D10 -- Child hierarchy id

SELECT
*
FROM 
  dbo.TableName
WHERE 
  @hid.IsDescendantOf(ParentHierarchyId) = 1

Answer 4

I wrote a user-defined table-valued function that expands a hierarchyid value into its constituent ancestors. The output can then be joined back on the hierarchyid column to get those ancestors specifically.

alter function dbo.GetAllAncestors(@h hierarchyid, @ReturnSelf bit)
returns table
as return
 select @h.GetAncestor(n.Number) as h
 from dbo.Numbers as n
 where n.Number <= @h.GetLevel()
  or (@ReturnSelf = 1 and n.Number = 0)

 union all

 select @h
 where @ReturnSelf = 1
go

To go about using it:

select child.ID, parent.ID
from dbo.yourTable as child
cross apply dbo.GetAllAncestors(child.hid, 1) as a
join dbo.yourTable as parent
   on parent.hid = a.h