sql-serverhierarchical-datahierarchyid

SQL Server - Get first node from HierarchyId field

Suppose I have the following HIERARCHYID column in my SQL Server table:

MyCol   
/1/1/   
/2/1/   
/3/1/1/ 
/3/1/2/ 
/3/2/1/1/   
/3/2/1/2/   
/4/1/   
/4/2/   
/5/1/   
/6/1/   
/7/1/   
/8/1/   
/8/2/

And I want to write a query that returns the following INT TopNode column - basically returning the top-level value of the MyCol column:

MyCol       TopNode
/1/1/       1
/2/1/       2
/3/1/1/     3
/3/1/2/     3
/3/2/1/1/   3
/3/2/1/2/   3
/4/1/       4
/4/2/       4
/5/1/       5
/6/1/       6
/7/1/       7
/8/1/       8
/8/2/       8

I'm fairly new to using the HierarchyId data type and am having an issue finding the correct function between GetDescendant, GetLevel etc to be able to return this integer.

How can I accomplish this in the easiest way possible?

Solution

  • Does this work in your case?

    DECLARE @T TABLE(X HIERARCHYID)
INSERT @T SELECT '/1/1/'   
INSERT @T SELECT '/2/1/'   
INSERT @T SELECT '/3/1/'  
INSERT @T SELECT '/3/2/'   
INSERT @T SELECT '/3/3/'   
INSERT @T SELECT '/3/4/'

SELECT 
    X.GetAncestor(X.GetLevel()-1),
    X.GetAncestor(X.GetLevel()-1).ToString()  
FROM @T

    You can then derive the int by casting from a simple replace -->

    TopLevel = CAST(REPLACE(X.GetAncestor(X.GetLevel()-1).ToString(),'/','') AS INT)