How to get recursivelevel using SQL Server 2012 hierarchyid?

Question

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/MomID/ respectively. Which means the students fatherid and the motherID. My Table Name is students.

this is my sample NAMEHID column

/2/8/5/

/5/11/12/

/8/7/9/

I need a output like

NameID   |  RecursiveLevel
2                 0
5                 1
7                 2
8                 1
9                 2
11                2
12                2

From this Pic you can see the what is the RecursiveLevel. This tree representing something like ancestors of a specific node.

I need to show only the NameID and the Recursive level as out put. if there Any other additional information need to do this?

Streptomycin

Thats... not how hierarchyid is intended to be used. – Metastasis

am newbie to sql. this is one of task asked from my supervisor. Here i mentioned all the details wht he hv gave me. is there any wrong in this? – Streptomycin

The tree you're showing would naturally be represented in hierachyid with values such as /2/, /2/8/, /2/8/7/, /2/8/9/, etc. That's how it's intended to be used - each level is represented by adding new values onto the end of the preceding level. If you use that representation, it's trivial to call GetLevel and obtain the result. Unfortunately, that's not the representation you're using and so all of the features of this data type are now going to work against obtaining sensible results. – Metastasis

@Metastasis thank u for the information. now i got clear information about hierachyid. Cant we do this task by taking above column NAMEHid as a sample of the main column.? – Streptomycin

As others have suggested, you HierarchyID representation and the graphical representation of the tree don't match. Which is correct? The output seems to match the graph. If that's the case (i.e. that's the case you're trying to solve), let me know and I can help. – Soberminded

@BenThul the graphical representation that i used only for pointing out what is recursive level only. HierarchyID representation is correct one. can you help me. – Streptomycin

What I'm saying is that even your words don't match your hierarchyid. That is, if a given element has two parents, you cannot represent that with a single hierarchyid. Since to do so would imply that one of the parents is a child of the other. Which isn't to say that you can't do what you're looking to do with hierarchyid, but you need more than one (you'll need one to represent each parent). So, without hierarchyid, what does your data look like? A simple table like create table(ID int, FatherID int, MotherID int); populated with sample data will suffice. – Soberminded

Answer 1

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

DECLARE @SampleData TABLE
(
   NAMEHID varchar(30)
)

INSERT INTO @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] , 0 AS Lvl FROM temp t
   WHERE t.Pos = 1
   AND NOT EXISTS ( 
                SELECT * FROM temp t2 
                WHERE  t2.[Value] = t.[Value] AND t2.Pos > 1
               )  -- root ID
   UNION ALL 

   SELECT t2.NameHID, t2.[Value], cte.Lvl + 1 
   FROM  cte 
   INNER JOIN temp t ON cte.[Value] = t.[Value] AND t.Pos = 1
   INNER JOIN temp t2 ON t.NameHID = t2.NameHID AND t2.Pos > 1
)
SELECT cte.[Value] AS NameId, cte.Lvl 
FROM cte 
ORDER BY cte.Lvl, cte.[Value]
OPTION(MAXRECURSION 0)

Split function

CREATE FUNCTION [dbo].[SplitString] (@Text varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
   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

Bahadur

thank you for the answer. and i hav a doubt why i couldn't use where clause near the SELECT * FROM @SampleData sd ? – Streptomycin

this is the where clause SELECT * FROM @SampleData sd where sd.NAMEHID < 3 the error is Incorrect syntax near the keyword 'CROSS'. Incorrect syntax near 'cr'. – Streptomycin

I think where clause must be after ) cr. Try it. – Bahadur