/*
The organizational chart would look like this as a directed graph:
Albert (1,12)
/ /
/ /
Bert (2,3) Chuck (4,11)
/ | /
/ | /
/ | /
/ | /
Donna (5,6) Eddie (7,8) Fred (9,10)
*/
-- Transform recursion into non-recursion
-- Pre-Order tree traverse
-- 6. To convert an adjacency list model into a nested set model, use a push down stack algorithm. Assume that we have these tables:
declare @tree table(emp varchar(30), boss varchar(30))
insert @tree
select 'Albert', NULL
union all select 'Bert', 'Albert'
union all select 'Chuck', 'Albert'
union all select 'Donna', 'Chuck'
union all select 'Eddie', 'Chuck'
union all select 'Fred', 'Chuck'
-- stack hold the info about nested_set
-- stack_index < 0 means the entry is pop out of the stack
declare @stack table(stack_index int, emp varchar(30), lft int, rgt int)
declare @counter int, @max_counter int, @current_top int
select @counter = 2, @max_counter = 2 * (select count(*) from @tree), @current_top = 1
insert @stack select 1, emp, 1, NULL from @tree where boss is null
delete from @tree where boss is null
select [@counter] = 1, [@current_top] = 1, * from @stack
-- check weather stack is empty (get rid of the variable @max_counter)
-- while exists (select * from @stack where stack_index > 0)
while @counter <= (@max_counter)
begin
-- does the employee(stack_index = @current_top) has any subordinate ??
if exists(select * from @tree t where (t.boss = (select emp from @stack where stack_index = @current_top)))
begin
-- push when top has subordinates and set lft value
insert @stack select (@current_top+1), min(t.emp), @counter, NULL
from @tree t where (t.boss = (select emp from @stack where stack_index = @current_top))
-- delete the tree entry corresponding to the aforementioned subordinate
delete from @tree where emp = (select emp from @stack where stack_index = @current_top + 1)
select @current_top = @current_top + 1
end
else
begin -- pop the stack and set rgt value
update @stack set rgt = @counter, stack_index = -stack_index /*pop stack */ where stack_index = @current_top
select @current_top = @current_top - 1
end
select [@counter] = @counter, [@current_top] = @current_top, * from @stack
select @counter = @counter + 1
end
select * from @stack