Finding a hierarchy in SQL Server

thanks goes out to the book ‘SQL Cookbook’ – Lucky Chapter 13 Hierarchical Queries …

with  x (tree,childid,depth)
as  (
select  cast(name as varchar(500)),
childid, 0
from vw_users_parent_children
union  all
select  cast(x.tree+’–>’+e.name as varchar(500)),
e.childid, x.depth+1
from  vw_users_parent_children e, x
where x.childid = e.children_id
)
select distinct tree leaf___branch___root
from x
where depth >= 1
order by tree asc

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.