You'll do something called a Recursive CTE.    here is a simple example, and you can google Recursive CTE for lots more:

http://blog.sqlauthority.com/2008/07/28/sql-server-simple-example-of-recursive-cte/