There is no good solution for manipulating trees from SQL. SQL is natively a set-based system and tree manipulation required row-oriented operations. There are no elegant solutions - yet.

The BOL has one solution. Check out the topic Expanding Hierarchies. There have been a couple of articles address the issue in SQL Server magazine. Joe Celko addresses the issue in his book SQL For Smarties. I've actually implemented his solution. It works very well for non-volitle data sets.

-Mike