2

I have table structure similar to this

enter image description here

I want to build permission tree using sql query.

Here is sample input/output list

enter image description here

Is there any way to pull this data using sql query without cursor?

Note: The whole table structure drill down to this tree

enter image description here

and the objective here is to display every child node for given node.

TechnoCrat
  • 2,055
  • 4
  • 23
  • 35

1 Answers1

2

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
Bozman
  • 477
  • 4
  • 16
  • Can you please explain it with some example/query? – TechnoCrat Jan 02 '14 at 16:29
  • there that should work without using stuff. but you will have an extra comma at the end. – Bozman Jan 02 '14 at 16:48
  • 1
    you should create a stored procedure or function that takes that parameter. this was just a self contained example. you can change the 20 to whatever you want to test. – Bozman Jan 02 '14 at 16:56