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

By | August 25, 2017

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.

Here is the query which will return all the ancestors of a records whose hierarchyid is passed.

 DECLARE @hierarchyidValue AS hierarchyid
 SET @hierarchyidValue = 0x5AD6D0
 SELECT Name From TableName T WHERE @hierarchyidValue.IsDescendantOf(T.Hierarchy) = 1

 

Category: SQL

Leave a Reply

Your email address will not be published. Required fields are marked *