Converting a hierarchy into a string, the lexical order of which coincides with the hierarchy order of the depth of the hierarchy

Question

I am wondering if there is a reasonable, effective way to convert the hierarchy value to a string whose lexical ordering maintains the natural order of depth.

Thanks!

Answer 1

According to the code below (run it on AdventureWorks2008) you can convert to varbinary and then to nvarchar using style 1. Without style 1, it does not order it correctly.

select convert(nvarchar(20),convert(varbinary(20),OrganizationNode,1),1) as OrderableString, OrganizationNode.ToString() as ReadableString, convert(nvarchar(20),OrganizationNode) as ReadableString2 from humanresources.employee order by strNode; 

Some of these orders give:

 OrderableString ReadableString ReadableString2 0x7AD744 /3/1/1/8/ /3/1/1/8/ 0x7AD74C /3/1/1/9/ /3/1/1/9/ 0x7AD754 /3/1/1/10/ /3/1/1/10/ 0x7AD75C /3/1/1/11/ /3/1/1/11/ 0x7AD764 /3/1/1/12/ /3/1/1/12/ 0x7ADA /3/1/2/ /3/1/2/ 0x7ADAB0 /3/1/2/1/ /3/1/2/1/ 0x7ADAD0 /3/1/2/2/ /3/1/2/2/