.

Working with hierachyid in SQL

Using hierachyid to store things in tree structures:

declare @myHid1 hierarchyid;
declare @myHid2 hierarchyid;
set @myHid1 = '/1/2/3/1/';
set @myHid2 = '/1/2/2/4/';
select  hyid.ToString() from
 (select @myHid1 as hyid
 union
 select @myHid2 as hyid) as a
  order by hyid

Returns (depth first)

(hyid)
/1/2/2/4/
/1/2/3/1/
  ** Please note all hierachyids ends on a /

– adding length produces breadth first sort

declare @myHid1 hierarchyid;
declare @myHid2 hierarchyid;
set @myHid1 = '/1/2/2/1/';
set @myHid2 = '/1/1/2/1/2/';
select hyid.ToString() from
 (select 4 as len, @myHid1 as hyid
 union
 select 5 as len, @myHid2 as hyid) as a
 order by len, hyid

If you have a table as follow:

Table Staff
(
id int identity primary key,
name nvarchar(50),
node hierarchyid
)
with records:
1, 'Mike', '/'
2, 'Sam', '/1/'
3, 'Pete', '/1/'

this is your code

select S.node.ToString() as node, S.name, S2.name as ReportTo
from Staff as S
join Staff as S2
on S2.node.ToString() = left(S.node.ToString(), 
	len(S.node.ToString()) + 1 -
	    charindex('/', reverse(S.node.ToString()),2 ))

With results:

node, staff name, staff boss name

also explore:
.. where node.IsDescendantOf(‘/3/’) = 1 and node.GetLevel() = 2 ..
select cast(‘/2/’ as hierarchyid)
.GetDescendantOf(‘/3/1/’,'/2/3/’).ToString()
SELECT hierarchyid::Parse(‘/5/4/3/2/1/’)
GetRoot()

depth-first

depth-first

breadth-first

breadth-first

 

What's your thoughts on this?

*

Protected by WP Anti Spam