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