327
Chapter 13: Working with Hierarchies
13
13 0x5AE368 4 Janice Galvin
14 0x5AE5 3 Michael Sullivan
15 0x5AE7 3 Sharon Salavaria
16 0x68 1 David Bradley
17 0x6AC0 2 Kevin Brown
18 0x6B40 2 John Wood
19 0x6BC0 2 Mary Dempsey
20 0x6C20 2 Wanida Benshoof
21 0x6C60 2 Terry Eminhizer
22 0x6CA0 2 Sariya Harnpadoungsataya
23 0x6CE0 2 Mary Gibson
24 0x6D10 2 Jill Williams
25 0x78 1 James HamiltonIn its raw form, the HierarchyID data type doesn’t tell you anything. What you can do is
use the ToString() method to convert the data from hex to text.SELECT TOP 25
e.BusinessEntityID AS BusEntID,
e.OrganizationNode AS OrgNode,
e.OrganizationNode.ToString() AS 'Hierarchy',
e.OrganizationLevel AS OrgLevel,
p.FirstName + ' ' + p.LastName AS 'Name'
FROM HumanResources.Employee e
INNER JOIN Person.Person p ON e.BusinessEntityID = p.BusinessEntityIDThe third column now looks much like the materialized path pattern. You know the
materialized path pattern well. You deal with it every day when you work with directory
structures (such as in Windows Explorer).BusEntID OrgNode HierarchyID OrgLevel Name
-------- ------- ----------- -------- --------1 0x / 0 Ken Sánchez
2 0x58 /1/ 1 Terri Duffy
3 0x5AC0 /1/1/ 2 Roberto Tamburello
4 0x5AD6 /1/1/1/ 3 Rob Walters
5 0x5ADA /1/1/2/ 3 Gail Erickson
6 0x5ADE /1/1/3/ 3 Jossef Goldberg
7 0x5AE1 /1/1/4/ 3 Dylan Miller
8 0x5AE158 /1/1/4/1/ 4 Diane Margheim
9 0x5AE168 /1/1/4/2/ 4 Gigi Matthew
10 0x5AE178 /1/1/4/3/ 4 Michael Raheem
11 0x5AE3 /1/1/5/ 3 Ovidiu Cracium
12 0x5AE358 /1/1/5/1/ 4 Thierry D'Hers
13 0x5AE368 /1/1/5/2/ 4 Janice Galvin
14 0x5AE5 /1/1/6/ 3 Michael Sullivan
15 0x5AE7 /1/1/7/ 3 Sharon Salavaria
16 0x68 /2/ 1 David Bradleyc13.indd 327c13.indd 327 7/30/2012 4:47:04 PM7/30/2012 4:47:04 PM
http://www.it-ebooks.info