I have solved this problem a number of times using recursion on the client and have always wanted to better solution for handling on the server side. Handling it on the client requires that you bring back all of the data and recurse through it. Again I was faced with the problem this week... and I thought I would poke around to see if there was a better way to solve the problem. It looks SQL Server 2005 now supports Common Table Expressions, which allows for recursion. Here are a couple of articles I found explaining how to do it:
SQL Server 2005 Recursion Functions
Hierarchical Queries in SQL Server 2005