How To Get Recursivelevel Using Sql Server 2012 Hierarchyid?

I know its quite challenging, Is there any sql specialist please kindly help me to solve this. I Have hierarchyID like below in NameHID column. Its representing /NameID/dadID/MomI

Solution 1:

Your data is not correct hierachyid. You could do your job by using a split function and recursive cte

DECLARE@SampleDataTABLE
(
   NAMEHID varchar(30)
)

INSERTINTO@SampleData
(
   NAMEHID
)
VALUES
('/2/8/5/'),('/5/11/12/'),('/8/7/9/')

;with temp AS
(
   SELECT*FROM@SampleData sd
   CROSS APPLY
   (
      SELECT*FROM [dbo].[SplitString](STUFF(LEFT(sd.NameHID,LEN(sd.NameHID) -1),1,1,''),'/')
   ) cr
)
,cte AS
(
   SELECT t.NameHID, t.[Value] , 0AS Lvl FROM temp t
   WHERE t.Pos =1ANDNOTEXISTS ( 
                SELECT*FROM temp t2 
                WHERE  t2.[Value] = t.[Value] AND t2.Pos >1
               )  -- root IDUNIONALLSELECT t2.NameHID, t2.[Value], cte.Lvl +1FROM  cte 
   INNERJOIN temp t ON cte.[Value] = t.[Value] AND t.Pos =1INNERJOIN temp t2 ON t.NameHID = t2.NameHID AND t2.Pos >1
)
SELECT cte.[Value] AS NameId, cte.Lvl 
FROM cte 
ORDERBY cte.Lvl, cte.[Value]
OPTION(MAXRECURSION 0)

Split function

CREATEFUNCTION[dbo].[SplitString] (@Text varchar(max),@Delimiter varchar(10))
ReturnsTableAsReturn (  
   Select Pos = Row_Number() over (Order By (Select null))
        ,Value = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
   From (Select x = Cast('<x>'+ Replace(@Text,@Delimiter,'</x><x>')+'</x>' as xml).query('.')) as A 
   Cross Apply x.nodes('x') AS B(i)
);

Demo link: http://rextester.com/KPX84657