You might want to try a Recursive CTE, check out BOL for examples, I think its Example D or E on the BOL WITH (common Table Expression) help page.