-- Convert HierarchyID to string and reverse
SELECT TOP 1000 [BusinessEntityID]
,[NationalIDNumber]
,[OrganizationNode]
,[OrganizationNode].ToString() as AlphaHierarchyID
,[OrganizationLevel]
,hierarchyid::Parse([OrganizationNode].ToString()+'1/') AS AlphaToHierarchyID
FROM [AdventureWorks2008].[HumanResources].[Employee]
ORDER BY BusinessEntityID
/* BusinessEntityID NationalIDNumber OrganizationNode AlphaHierarchyID
OrganizationLevel AlphaToHierarchyID
1 295847284 0x / 0 0x58
2 245797967 0x58 /1/ 1 0x5AC0
3 509647174 0x5AC0 /1/1/ 2 0x5AD6
4 112457891 0x5AD6 /1/1/1/ 3 0x5AD6B0
5 695256908 0x5ADA /1/1/2/ 3 0x5ADAB0
6 998320692 0x5ADE /1/1/3/ 3 0x5ADEB0
7 134969118 0x5AE1 /1/1/4/ 3 0x5AE158
......*/
------------
Source : sqlusa
No comments:
Post a Comment