Have a look at this TechNet article... http://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

It shows sample data and the results of a recursive CTE (I think that this is what you are after)