3

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+ bytes).

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

mbourgon
  • 1,286
  • 2
  • 17
  • 35
  • And the reason I'm doing this: due to the amount of logic in dbo.SPLIT (what's above is a drastic simplication), I'm trying to avoid running the scalar-valued function version of SPLIT, which looks like `select dbo.split('a',1,'1,a15,b20,c40;2,a25,d30;3,e50')`, and winds up being called 12 times ("'a',1", "'a',2","'a',3","'a',4","'c',1","'c'2",...),. Which, when queried against a table with a million rows, has got to be A Bad Thing. – mbourgon Sep 29 '11 at 16:17
  • I guess I'm not understanding something...you would need to call the function with new parameters anyway so a CROSS APPLY won't help you. How are the parameters being passed into the function in the first place? Can you give an overview of the process involved? – Wil Sep 29 '11 at 16:54
  • instead of using parameters, I want to feed it a field from a table. The current process basically looks like `select a,b,dbo.splitscalar('a',1,stringofdata), dbo.splitscalar('a', 2, stringofdata), dbo.splitscalar('b',1,stringofdata)...` (12 times total), and since the string is several hundred bytes, I figure changing to the CROSS APPLY and calling the function 3 times instead might be faster. – mbourgon Sep 30 '11 at 15:01
  • Although this doesn't answer your question directly, I used [this SO post](http://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) to dynamically create columns for the pivot table. Maybe it will help. – Matthew Feb 24 '14 at 13:28

1 Answers1

4

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
ClearLogic
  • 3,616
  • 1
  • 23
  • 31