T-SQL - CROSS APPLY to a PIVOT? (using pivot with a table-valued function)?

I have a table-valued function, basically a split-type function, that returns up to 4 rows per string of data.

So I run:

select * from dbo.split('a','1,a15,b20,c40;2,a25,d30;3,e50')

I get:

Seq  Data
1    15
2    25

However, my end data needs to look like

15 25

so I do a pivot.

select [1],[2],[3],[4] 
from dbo.split('a','1,a15,b20,c40;2,a25,d30;3,e50')
pivot (max(data) for seq in ([1],[2],[3],[4])) 
as pivottable

which works as expected:

1    2
---  ---
15   25

HOWEVER, that's great for one row. I now need to do it for several hundred records at once. My thought is to do a CROSS APPLY, but not sure how to combine a CROSS APPLY and a PIVOT.

(yes, obviously the easy answer is to write a modified version that returns 4 columns, but that's not a great option for other reasons)

Any help greatly appreciated.

And the reason I'm doing this: the current query uses as scalar-valued version of SPLIT, called 12 times within the same SELECT against the same million rows (where the data string is 500+ byt开发者_StackOverflow社区es).

So far as I know, that would require it scan the same 500bytes * 1000000rows, 12 times.

This is how you use cross apply. Assume table1 is your table and Line is the field in your table you want to split

SELECT * fROM table1 as a
    cross apply dbo.split(a.Line) as b  
    pivot (max(data) for seq in ([1],[2],[3],[4]))  as p