I am posting this not only for personal reference, but because I conjured it as a result of the T-SQL Explode function I described in my last post. Previously, I had taken the results from the Explode and pushed them into a temp table; But thanks to SQL Server 2005
CROSS APPLY functionality, we can cut the temp table out of the picture and join directly against the function.
Note: I tried an explicit join using the
ON keyword, but SQL was not very happy about it. Using the implicit join within the
WHERE clause works perfectly fine.
from table t
cross apply explode(‘value1, value2, value3’) e
where e.item = t.foreign_key