title: T-SQL - CROSS APPLY to a PIVOT? (using pivot with a table-valued function)?
body: <p>I have a table-valued function, basically a split-type function, that returns up to 4 rows per string of data.</p>
  
  <p>So I run:</p>
  
  <pre><code>select * from dbo.split('a','1,a15,b20,c40;2,a25,d30;3,e50')
  </code></pre>
  
  <p>I get:</p>
  
  <pre><code>Seq  Data
  1    15
  2    25
  </code></pre>
  
  <p>However, my end data needs to look like </p>
  
  <pre><code>15 25
  </code></pre>
  
  <p>so I do a pivot.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>which works as expected:</p>
  
  <pre><code>1    2
  ---  ---
  15   25
  </code></pre>
  
  <p>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.  </p>
  
  <p>(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)</p>
  
  <p>Any help greatly appreciated.</p>
  
  <p>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+ bytes).  </p>
  
  <p>So far as I know, that would require it scan the same 500bytes * 1000000rows, 12 times.</p>