The usual solution for this is a recursive CTE.

Look at

http://www.sqlservercentral.com/articles/Development/recursivequeriesinsqlserver2005/1760/

for example.

If you post some proper test data it will be easier for people to help!

Also, do you want help designing an sp to display the data, or to make the changes?