I have table structure similar to this
I want to build permission tree using sql query.
Here is sample input/output list
Is there any way to pull this data using sql query without cursor?
Note: The whole table structure drill down to this tree
and the objective here is to display every child node for given node.
you can use a self referencing common table expression and STUFF to get the desired output. ordering can be done using a sequential string. i'll edit the answer with an example if that will help more.
declare @emps table (uid int, boss int);
insert into @emps (uid,boss)
values (1,6),(2,6),(3,6),(4,6),(5,6),(7,9),(8,9),(6,15),(9,15),(15,20),(17,20);
declare @user int = 20;
declare @output varchar(100) = '';
WITH cte (uid,ord)
AS ( SELECT uid, 0
FROM @emps
WHERE boss = @user
UNION ALL
SELECT e.uid, cte.ord + 1
FROM @emps e
INNER JOIN cte ON cte.uid = e.boss)
SELECT @output = @output + convert(varchar,uid) + ','
FROM cte
order by ord
select @output
