Home
Manage Your Code
Snippet: DISPLAY HIERARCHY DATA (SQL)
Title: DISPLAY HIERARCHY DATA Language: SQL
Description: DISPLAY HIERARCHY DATA IN A RECURSIVE CHILD PARENT COLUMNS Views: 319
Author: elameen elsayed Date Added: 10/4/2011
Copy Code  
declare @t table (id int, parent int)
insert @t (id, parent) 
SELECT  EmployeeID,ReportsTo
FROM NORTHWIND..Employees
; with cte as (
    select  id, parent
    ,       cast(RIGHT(REPLICATE(' ',12) + 
                 CONVERT(varchar(12),id),12) as varchar(max)) Path
    from    @t
    where   parent is null
    union all
    select  child.id, child.parent
    ,       parent.Path + RIGHT(REPLICATE(' ',12) + 
                                CONVERT(varchar(12),child.id),12) as Path
    from    @t child
    join    cte parent
    on      parent.id = child.parent
)
select  *
from    cte
order by        Path
Usage
JUST REPLACE VALUES WITH YOURS
Notes
I AM NOT THE AUTHOR OF THIS SNIPPET I FOUND IT IN A THREAD AT STACKOVERFLAW.COM