How do you get all ancestors of a node using SQL Server 2008 hierarchyid?
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