Recursive concatenation of parent elements

Question

I have a table that looks like this:

ID |Name |Parent 1 |A |NULL 2 |B |NULL 3 |C |1 4 |D |1 5 |E |3 6 |F |5 

I would like the view to return this:

 ID |Name |ParentNames 1 |A | 2 |B | 3 |C |A 4 |D |A 5 |E |A > C 6 |F |A > C > E 

I tried to leave the connection representing the first parent id and left it myself, but that didn't work.

Is there a way to do this without a stored procedure / function? I have ~ 15k lines with ~ 0-5 parents, but I would rather not hard code a maximum of 5 parents.

Answer 1

You can use recursive CTE .

 declare @T table(ID int, Name char(1), Parent int); insert into @T values (1 ,'A' ,NULL), (2 ,'B' ,NULL), (3 ,'C' ,1), (4 ,'D' ,1), (5 ,'E' ,3), (6 ,'F' ,5); with C as ( select ID, Name, Parent, cast('' as varchar(max)) as ParentNames from @T where parent is null union all select T.ID, T.Name, T.Parent, C.ParentNames + ' > ' + C.Name from @T as T inner join C on C.ID = T.Parent ) select ID, Name, stuff(ParentNames, 1, 3, '') as ParentNames from C; 

Answer 2

Not sure if this is possible for you, but have you looked at the new hierarchy identifier?

http://msdn.microsoft.com/en-us/library/bb677290.aspx