Susan,

Cole kinda hijacked your post... do you still need help or are you all set?

Missing a key piece of information here... basically, what does all that code do?  I see stuff like 'Level 3' in it... is it meant to produce some sort of...

You bet, Sakthi... thanks for the feedback.

First, the lecture...

If you want good quality help in a timely fashion, you must take the time to post your data in the form of a CREATE TABLE along with...

What have you tried, so far?

Funny how this works... everyone seems to say that performance isn't all that important... but, why do they buy the high performance hardware if performance is not important?  And why do so...

--===== Setup a test table to demonstrate the solution.
     -- THIS IS NOT PART OF THE SOLUTION!!! It's just test data!
DECLARE @yourtable TABLE
        (ID INT PRIMARY KEY, [User] INT, Test...

Heh... I used to seriously question cursors… now, I LOVE cursors and RBAR While Loops, too! Even better than that, I LOVE code that looks set-based (no While loops)...

Vamshi,

Did you ever resolve your problem?  If not, you might want to check out "Expanding Hierarchies" in Books Online.

Thanks, Markus... but tell us... what did you have in mind?  Both scripts return the necessary information (we thought) you were requesting... what's missing?

Maybe in the last SELECT but the rest of it is based almost exactly on what you did, Lowell.  I just changed it a bit just 'cause I like changing...

Shoot... way too late for that request, eh

Heh... triangular joins have been around a lot longer than I've been writing SQL so I can't take any credit for them.  Actually, I don't like either method just because they...

OK... Now I see and I've got a bit of egg on my face... there's actually a couple of errors in Peter's code

Sorry...

He even changed the name!!!  Did you look at THIS piece of code?