Using Apply: CROSS APPLY and OUTER APPLY

The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query.

http://msdn.microsoft.com/en-us/library/ms175156.aspx