DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to convert HierarchyID to string?

Execute the following Microsoft SQL Server T-SQL scripts in SSMS Query Editor to demonstrate the converting HierarchyID to strings and string to HierarchyID conversion.

 

-- 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

......*/

------------

Related articles:

http://www.sqlusa.com/bestpractices2008/orgchart/

Hierarchical Data (SQL Server)

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE