Microsoft SQL Server cross apply Join table rows with dynamically generated rows from a cell
Example
CROSS APPLY enables you to "join" rows from a table with dynamically generated rows returned by some table-value function.
Imagine that you have a Company table with a column that contains an array of products (ProductList column), and a function that parse these values and returns a set of products. You can select all rows from a Company table, apply this function on a ProductList column and "join" generated results with parent Company row:
SELECT *
FROM Companies c
CROSS APPLY dbo.GetProductList( c.ProductList ) p
For each row, value of ProductList cell will be provided to the function, and the function will return those products as a set of rows that can be joined with the parent row.
