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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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