Dealing with hierarchies in SQL Server?
I know, this stuff is rather old, however it has been very useful for me so I thought to point it out.
In case self-joining tables are everyday life for you and you need to have a way to query them reasonably, check these out.
Expanding Hierarchies [MS] for background
http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_14_5yk3.asp
And the true winner,
Expanding Hierarchies [WindowsITPro]
http://www.windowsitpro.com/SQLServer/Article/ArticleID/15715/15715.html
I use a solution very similar to the one pointed in the latter article.