1

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.

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

Manojkanth
  • 1,071
  • 11
  • 27
  • Thats... not how `hierarchyid` is intended to be used. – Damien_The_Unbeliever May 17 '17 at 05:53
  • 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? – Manojkanth May 17 '17 at 05:56
  • 1
    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. – Damien_The_Unbeliever May 17 '17 at 06:05
  • @Damien_The_Unbeliever 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.? – Manojkanth May 17 '17 at 06:11
  • 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. – Ben Thul May 17 '17 at 12:58
  • @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. – Manojkanth May 18 '17 at 05:35
  • 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. – Ben Thul May 18 '17 at 14:54

1 Answers1

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

TriV
  • 5,118
  • 2
  • 10
  • 18
  • thank you for the answer. and i hav a doubt why i couldn't use where clause near the SELECT * FROM @SampleData sd ? – Manojkanth May 18 '17 at 06:07
  • 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'.` – Manojkanth May 18 '17 at 06:24
  • I think where clause must be after `) cr`. Try it. – TriV May 18 '17 at 06:32