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

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.)

Solution

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.