333
Chapter 13: Working with Hierarchies
13
One of the diffi culties when dealing with hierarchical data is that as it grows it can become
more diffi cult to determine where the members are in the hierarchy. This is where the
GetLevel() method comes in, which returns the value of how many levels down each row
is in the hierarchy.The following code uses the GetLevel() method as the second column in the SELECT
query to return the levels down each row in the hierarchy.SELECT OrganizationNode.ToString() AS 'Hierarchy',
OrganizationNode.GetLevel() AS EmpLevel,
p.FirstName + ' ' + p.LastName AS 'Name',
e.JobTitle
FROM HumanResources.Employee e
INNER JOIN Person.Person p ON e.BusinessEntityID = p.BusinessEntityID
GOHierarchy EmpLevel Name JobTitle
--------- -------- ---- ----------------------/ 0 Ken Sánchez Chief Executive Officer
/1/ 1 Terri Duffy Vice President of Engineering
/1/1/ 2 Roberto Tamburello Engineering Manager
/1/1/1/ 3 Rob Walters Senior Tool Designer
/1/1/2/ 3 Gail Erickson Design Engineer
/1/1/3/ 3 Jossef Goldberg Design Engineer
/1/1/4/ 3 Dylan Miller Research and Development
Manager
/1/1/4/1/ 4 Diane Margheim Research and Development
Engineer
/1/1/4/2/ 4 Gigi Matthew Research and Development
Engineer
/1/1/4/3/ 4 Michael Raheem Research and Development
Manager
/1/1/5/ 3 Ovidiu Cracium Senior Tool Designer
/1/1/5/1/ 4 Thierry D'Hers Tool Designer
/1/1/5/2/ 4 Janice Galvin Tool Designer
/1/1/6/ 3 Michael Sullivan Senior Design Engineer
/1/1/7/ 3 Sharon Salavaria Design Engineer
/2/ 1 David Bradley Marketing Manager
/2/1/ 2 Kevin Brown Marketing Assistant
/2/2/ 2 John Wood Marketing Specialist
/2/3/ 2 Mary Dempsey Marketing Assistant
/2/4/ 2 Wanida Benshoof Marketing Assistant
/2/5/ 2 Terry Eminhizer Marketing Specialist
/2/6/ 2 Sariya Harnpadoungsataya Marketing Specialist
/2/7/ 2 Mary Gibson Marketing Specialist
/2/8/ 2 Jill Williams Marketing Specialist
/3/ 1 James Hamilton Vice President of Production
/3/1/ 2 Peter Krebs Production Control Manager
/3/1/1/ 3 Jo Brown Production Supervisor - WC60c13.indd 333c13.indd 333 7/30/2012 4:47:05 PM7/30/2012 4:47:05 PM
http://www.it-ebooks.info