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 a开发者_运维技巧ncestors 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.)

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.

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

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

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

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

Perfecting Ben Thui's answer which I find to be the best one so far...

The approach below allows to retrieve not only one but potentially several leaf rows and their ascendants in one single query.

Create Or Alter Function dbo.GetAllAncestors
(
    @Path       HierarchyId,
    @WithSelf   Bit = 1,
    @MinLevel   Int = 0,
    @MaxLevel   Int = Null
)
Returns Table
As
Return

With Ancestor As
(
    Select  @Path As Path
    Union All

    Select  Path.GetAncestor(1)
    From    Ancestor
    Where   Path.GetLevel() > 0
)

Select  Path, Path.GetLevel() As Level
From    Ancestor
Where   (@WithSelf = 1 Or Path <> @Path)
And     Path.GetLevel() >= Case When @MinLevel < 0 Or @MinLevel Is Null Then 0 Else @MinLevel End
And     (@MaxLevel Is Null Or Path.GetLevel() <= @MaxLevel)

To use:

-- This assumes the table has a Path HierarchyId colum, and the values are unique and indexed.

-- If you know the path
Select *
From MyTable
Where Path In
(
    Select Path From dbo.GetAllAncestors(@ThePath, Default, Default, Default)
)

-- If you don't know the path
Select *
From MyTable t1
Where Path In 
(
    Select Path
    From   MyTable t2
           Cross Apply dbo.GetAllAncestors(t2.Path, Default, Default, Default)
    Where  /* Find the leaf record(s) here.
              Note that if multiple rows match, they will all be returned as well as their parents in a single roundtrip. */
)

DECLARE @hid_Specific HIERARCHYID 
SET @hid_Specific = '/1/1/3/1/';

SELECT hrchy_id,* FROM tblHierarchyData 
WHERE PATINDEX(hrchy_id.ToString() + '%', @hid_Specific.ToString()) = 1