Simple but effective–just follow the guidelines in this post. Performance is great, much better by far than using sub-queries. I found a marginal performance dip using this method versus the nine-fold performance hit using a sub-query.
For those interested, I found this article and related tutorial from Eric White to be quite fascinating. I went through the tutorial this weekend and thought some of you might also be interested. Eric gives reasons for using functional programming (fp) over conventional object-oriented programming (oop); his most common reason is for performing transformations of data from one sort to another. Other highlights are more in-depth looks at extension methods, Linq and Office Open XML. (While you might wonder why you’d use fp instead of XSLT, the concepts can also apply to transforming objects/collections to other objects/collections using lazy evaluation and deferred execution).
SQL Server Central recently posted an article by Jeff Moden on using a Tally Table to iterate through database rows rather than a WHILE loop or CURSOR. The article defines tally tables and explains both how to create them and use them. Jeff even shows performance comparisons with a WHILE loop.
For those unfamiliar with tally tables, a tally table is generally used to parse data stored in a comma-delimited format but can also be used for other iterative actions. The tally table iterations also use set math and thus keep the CPU usage and row counts low consistently.
The next time you find yourself needing to iterate through table rows, try using a tally table.