How do you get all ancestors of a node using SQL Server 2008 hierarchyid?
Solution 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.
Solution 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
Solution 3
Declare @hid hierarchyid=0x5D10 -- Child hierarchy id
SELECT
*
FROM
dbo.TableName
WHERE
@hid.IsDescendantOf(ParentHierarchyId) = 1
Solution 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
marc esher
Updated on July 26, 2021Comments
-
marc esher almost 3 years
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 correspondingIsAncestorOf()
function to return ancestors (and the absence of aGetAncestors()
function seems like quite an oversight.)