Parent/Child hierarchy tree view

Question

I have a 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

Id like to get a result set like this

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

I know I need to do a rank query to rank the levels and a self join but all I can find on the net is CTE recursion

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

Answer 1

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

Answer 2

Bit hacky 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 however you want to avoid recursion then an alternative approach is to implement a tree table with the relevant tree structure information - see http://www.sqlteam.com/article/more-trees-hierarchies-in-sql for a walk through

Answer 3

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 a tough one:). I expanded the example to include > 1 tree. Results looking good so far.