Sunday, June 15, 2014

Table functions and cross apply

So recently at work I came across a table that had a column that contained multiple ID's that were comma separated.
It looked something like the students column below.


Now luckily we already have a table function built that will parse this out for me. Similar to here. All I have to do is pass in the string and the delimiter and it will spit out a table.


The question is how to use it.

In it's simplest form it works like below.



So I could pull out the value for each record, pass it into a variable, pass that variable into the function, record the results into a separate table so I know what I had, and then have it loop through until all were done.
It would look like something below, except that you would have to have this loop through until you got through everything. 


Now that would be a pain to setup, and doesn't sound very efficient. There has to be a better way. Especially if I wanted to join these values onto another table. That is where Cross Apply comes in handy.

Cross Apply works similarly to a Cross Join. Except it is made to play nice with table functions.
Instead of passing in a set string or a string variable, you simply join the two tables together and pass in the column name you want split.

You can see below that my table named class has a list of student numbers. It split out each separated value into the column called Value.







What makes this handy is that I can now join this onto other tables. In this case I created a table called students. If I join them together I get results like below.



The above method is much simpler than trying to loop through and do one at a time. It works like a standard join, except that you use Cross Apply instead of Join. And of course you don't enter the 'ON' part of the join, just enter the column name into the table function.