SQL tree data struct(5): AdjList2NestedSet

/*
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