Hi,

Thanks for you help.

I've been playing with this, and you're right it does work but doesn't give me the solution to the problem i have. I've given @table as an example here to illustrate the problem. In my real world problem @table is actually the result of a complex query and isn't stored in a table (temporary or otherwise). I have many more columns than outlined above and the query returns ~40k rows.

I was hoping to be able to do something in-line with a CROSS APPLY type solution but haven't been able to crack it. I could write the result of the query to a temporary or even a persisted table and then use the CTE as you suggest but there's write overhead invovled with that, that i'd like to avoid if possible.

Is there a way to do this without a CTE or am I hoping for a solution that simply isn't possible?

Thanks,

Simon