Microsoft SQL Server 2012 Bible

HierarchyID Methods

The hierarchyid data type includes several methods that easily and quickly help to navigate hierarchical data. You saw some examples of the IsDescendantOf method, which validate the existence of a descendant of a parent. The IsDescendantOf method returns true if the specified node is a descendant of parent.

The GetAncestor() method accepts an argument for the level you try to return and returns the hierarchyid representing the nth ancestor of the current level. In this example, use the GetAncestors() method to return the first level ancestors of the hierarchyid returned in the first SELECT statement.

DECLARE @CurrentEmployee hierarchyid

SELECT @CurrentEmployee = OrganizationNode
FROM HumanResources.Employee
WHERE OrganizationNode = ‘/5/'

SELECT OrganizationNode.ToString() AS ‘Hierarchy',
   p.FirstName + ‘ ‘ + p.LastName AS ‘Name',
   e.OrganizationLevel,
   e.JobTitle
FROM HumanResources.Employee e
INNER JOIN Person.Person p ON e.BusinessEntityID = p.BusinessEntityID
WHERE OrganizationNode.GetAncestor(1) = @CurrentEmployee

Hierarchy  Name       OrganizationLevel JobTitle
---------  ----       ----------------- --------

/5/1/    Stephanie Conroy  2        Network Manager
/5/2/    Karen Berg     2        Application Specialist
/5/3/    Ramesh Meyyappan  2        Application Specialist
/5/4/    Dan Bacon      2        Application Specialist
/5/5/    François Ajenstat  2        Database Administrator
/5/6/    Dan Wilson     2        Database Administrator
/5/7/    Janaina Bueno    2        Application Specialist
/5/1/    Gustavo Achong   2        Rock Star DBA

By changing the parameter ...