Learn Hierarchies in SQL server using HierarchyID

—GetLevel()                     :  Returns level of current node.

—GetAncestor(nth)           :  Returns nth ancestor of current node.

—IsDescendantOf(Pnode) : Returns all nodes where parent is Pnode

—GetDescendant(child1,child2) : Returns hierarchyid between child1 and child2. In this child1 and child2 can be NULL depending upon requirement.

—GetReparentedValue(oldRoot, newRoot): Return hierarchyid of node as per newRoot.

 

DECLARE @hid HIERARCHYID

SET @hid = '/1/2/3/'

--SELECT @hid as [HierarchyID]
--ToString and CAST on hierarchy id
--SELECT @hid [Var Binary],@hid.ToString() [As String],
--CAST(@hid AS VARCHAR) [As String by CAST]
--GetAncestor(nth) on hierarchy id
SELECT @hid.ToString(), @hid.GetAncestor(1).ToString() [Parent], @hid.GetAncestor(2).ToString() [Grand Parent], @hid.GetAncestor(3).ToString() [Root], @hid.GetAncestor(4).ToString() [No Parent]
WITH CTE(chain, NAME) AS (
 SELECT CAST('/1/' AS HIERARCHYID), 'Ram'
 
 UNION ALL
 
 SELECT '/2/', 'Ron'
 
 UNION ALL
 
 SELECT '/1/1/', 'Sahil'
 
 UNION ALL
 
 SELECT '/1/2/', 'Mohit'
 
 UNION ALL
 
 SELECT '/2/1/', 'Maze'
 
 UNION ALL
 
 SELECT '/', 'Boss'
 )

SELECT *
INTO #TEMP
FROM CTE

--INSERT INTO #TEMP VALUES('/1/2/1/','Don')
SELECT *, chain.ToString()
FROM #TEMP

--GetRoot, GetLevel() on hierarchyid
SELECT NAME, chain, chain.ToString() [String Value], HIERARCHYID::GetRoot().ToString() [Root], chain.GetLevel() [Level]
FROM #TEMP
ORDER BY chain.GetLevel()

--IsDescendantOf(node) on hierarchyid
DECLARE @node HIERARCHYID

SET @node = '/1/';

SELECT NAME, chain, chain.ToString() [String Value], HIERARCHYID::GetRoot().ToString() [Root], chain.GetLevel() [Level]
FROM #TEMP
WHERE chain.IsDescendantOf(@node) = 1
 AND chain != @node
 AND chain.GetLevel() = @node.GetLevel() + 1
ORDER BY chain.GetLevel()

--GetDescendant() on hierarchyid
DECLARE @leftNode HIERARCHYID
DECLARE @rightNode HIERARCHYID

SET @leftNode = '/2/1/';
SET @rightNode = '/2/1/';

WITH CTE (node)
AS (
 SELECT @leftNode.GetAncestor(1).GetDescendant(MAX(@rightnode), NULL) [GeneratedID]
 )
SELECT node, 'More'
FROM CTE

--GetReparentedValue(oldNode,newNode) on hierarchyid
DECLARE @oldNode HIERARCHYID, @newNode HIERARCHYID

SET @oldNode = '/1/';
SET @newNode = '/2/3/';

SELECT chain.ToString() [OldNode], NAME, chain.GetReparentedValue(@oldNode, @newNode).ToString() [NewNode]
FROM #TEMP
WHERE chain.IsDescendantOf(@oldNode) = 1

DROP TABLE #TEMP