331
Chapter 13: Working with Hierarchies
13
SELECT @EmpNode = OrganizationNode
FROM HumanResources.Employee
WHERE OrganizationNode = '/5/'SELECT e.BusinessEntityID AS BusEntID,
p.FirstName + ' ' + p.LastName AS 'Name',
e.OrganizationNode.ToString() AS 'Hierarchy',
e.OrganizationLevel,
e.JobTitle
FROM HumanResources.Employee e
INNER JOIN Person.Person p ON e.BusinessEntityID = p.BusinessEntityID
WHERE OrganizationNode.IsDescendantOf(@EmpNode) = 1BusEntID Name Hierarchy OrganizationLevel JobTitle
-------- ---- --------- ----------------- ---------263 Jean Trenary /5/ 1 Information Services
Manager
264 Stephanie Conroy /5/1/ 2 Network Manager
265 Ashvini Sharma /5/1/1/ 3 Network Administrator
266 Peter Connelly /5/1/2/ 3 Network Administrator
267 Karen Berg /5/2/ 2 Application Specialist
268 Ramesh Meyyappan /5/3/ 2 Application Specialist
269 Dan Bacon /5/4/ 2 Application Specialist
270 François Ajenstat /5/5/ 2 Database Administrator
271 Dan Wilson /5/6/ 2 Database Administrator
272 Janaina Bueno /5/7/ 2 Application Specialist
291 Gustavo Achong /5/1/ 2 Rock Star DBAA few methods of the hierarchyid data type have been discussed, but there are a few more,
plus it is a good idea to index the hierarchyid data type, which is discussed next.HierarchyID Methods
The hierarchyid data type includes several methods that easily and quickly help to navi-
gate 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 specifi ed 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
e xample, use the GetAncestors() method to return the fi rst level ancestors of the
hierarchyid returned in the fi rst SELECT statement.c13.indd 331c13.indd 331 7/30/2012 4:47:04 PM7/30/2012 4:47:04 PM
http://www.it-ebooks.info