Problem in displaying hierarchical records (SQL Server 2005) [SET BASED]

Question

I can make query to get the Master -Chile records. But how to display them in hierarchial fashion. Any example will help

I am using SQL Server 2005

Answer 1

There are more elgeant ways, but this quick modification will work:

;With bottomupParentChild AS 
( 
    SELECT EmpId AS parents, ReportsTo,CAST(EmpName AS VARCHAR(1000)) AS [Path],  0 AS [Level], EmpName 
    FROM @Employees 
    WHERE EmpId = @childNode 
    UNION ALL 
    SELECT i.EmpId AS parents, i.ReportsTo, CAST(gp.[Path] + '/' + i.EmpName AS VARCHAR(1000)) AS [Path],  
         gp.[Level]+1 AS [Level],i.EmpName 

    FROM @Employees i 
    JOIN bottomupParentChild gp ON i.EmpId = gp.ReportsTo 
) 
, cteRows (TotalRows)
 as (select count(*) - 1 from bottomupParentChild)

SELECT ABS(Level - TotalRows), REPLICATE('    ', ABS(Level - TotalRows)) + EmpName as [Hierarchy] 
FROM bottomupParentChild 
 cross join cteRows
ORDER BY  [Path] desc; 

I added a second cte that gets the count of rows from the first cte, and use it to "flip" the hierarchy.