Reproduction of a parent / child hierarchy tree

Question

My parents table looks like this:

CHILD_ID | PARENT_ID | NAME 1 | Null | Bill 2 | 1 | Jane 3 | 1 | Steve 4 | 2 | Ben 5 | 3 | Andrew 

I like to get a result set like this

 Bill ---Jane ------Ben ---Steve ------Andrew 

I know that I need to make a rank request for ranking levels and self-joining, but all I can find on the network is CTE recursion

I have done this in Oracle before, but not in MS SQL

Answer 1

The bit is hacked and can be improved, but hopefully it shows the principle ...

 ;with relation (childId, parentId, childName, [level], [orderSequence]) as ( select childId, parentId, childName, 0, cast(childId as varchar(20)) from @parents where parentId is null union all select p.childId, p.parentId, r.[level]+1, cast(r.orderSequence + '_' + cast(p.childId as varchar) as varchar(20)) from @parents p inner join relation r on p.parentId = r.childId ) select right('----------', ([level]*3)) +childName from relation order by orderSequence 

If you want to avoid recursion, an alternative approach is to implement a tree table with relevant information about the tree structure - see http://www.sqlteam.com/article/more-trees-hierarchies-in-sql for a walk through

Answer 2

 declare @pc table(CHILD_ID int, PARENT_ID int, [NAME] varchar(80)); insert into @pc select 1,NULL,'Bill' union all select 2,1,'Jane' union all select 3,1,'Steve' union all select 4,2,'Ben' union all select 5,3,'Andrew' union all select 6,NULL,'Tom' union all select 7,8,'Dick' union all select 8,6,'Harry' union all select 9,3,'Stu' union all select 10,7,'Joe'; ; with r as (     select CHILD_ID, PARENT_ID, [NAME], depth=0, sort=cast(CHILD_ID as varchar(max))     from @pc     where PARENT_ID is null     union all     select pc.CHILD_ID, pc.PARENT_ID, pc.[NAME], depth=r.depth+1, sort=r.sort+cast(pc.CHILD_ID as varchar(30))     from r     inner join @pc pc on r.CHILD_ID=pc.PARENT_ID where r.depth<32767 ) select tree=replicate('-',r.depth*3)+r.[NAME] from r order by sort option(maxrecursion 32767); 

This was hard:). I expanded the example to include> 1 tree. The results look good so far.

Answer 3

 WITH DirectReports (ParentUniqId, UniqId, SortID) AS ( SELECT e.ParentUniqId, e.UniqId, e.UniqId as SortID FROM Coding.Coding AS e WHERE isnull(ParentUniqId ,0)=0 UNION ALL SELECT e.ParentUniqId, e.UniqId,, d.SortID * 100 + e.UniqId as SortID FROM Coding.Coding AS e INNER JOIN DirectReports AS d ON e.ParentUniqId = d.UniqId ) SELECT ParentUniqId, Perfix,SortID FROM DirectReports order by rtrim(SortID) , uniqid