SQL Server 2005 - Table Functions in a Select statement

Question

I have a table called PurchaseOrderDetail.

TABLE PurchaseOrderDetail
 PurchaseOrderDetail int,
 Comments nvarchar(500)

In the Comments field of each item I have a ‘;’ separated list that stores A Contract Name, Contract No, License Name, License Version.

i.e.

PurchaseOrderDetail     Comments 
1                      'Microsoft Office Standard 2007;12%;;'
2                      'Microsoft Visio 2007;9%;;'

I also have a function called Split that takes a delimiter and a string and returns a table,

So calling this select * from Split(';', 'Microsoft Office Standard 2007;12%;;')

returns this

pn           s [ column names]
1            Microsoft Office Standard 2007
2            12%

I need to break this information out for each PurchaseOrderDetail and show them in a report

So something like this

select PurchaseOrderDetailID, cn.s as ContractName, cno.s as ContractNo
from dbo.PurchaseOrderDetail as pod
join dbo.Split(';', pod.Comments) as cn on cn.pn = 1
join dbo.Split(';', pod.Comments) as cno on cno.pn = 2

although that doesn’t run, but I hope it suggests intent.

I’d like my results to be:

PurchaseOrderDetailID   ContractName                    ContractNo
1                       Microsoft Office Standard 2007  12%

Is it possible, or am I tackling this the wrong way

Answer 1

You "join" to table-valued functions using the apply keyword. Simply pass your fields into the function rather than using an "ON" linking expression. An example from MSDN:

SELECT D.deptid, D.deptname, D.deptmgrid, ST.empid, ST.empname, ST.mgrid
FROM Departments AS D
    CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST;

OUTER APPLY is the equivalent of LEFT JOIN.

Edit

In your example, you could add the "cn = 1" and "cn = 2" criteria as a WHERE clause after APPLYing the function.

Answer 2

There is a reason why tables can have more than 1 column. It is a pain in the but to split values out of a common column all the time!

• alter your table
• add dedicated columns for your data
• UPDATE FROM and join in the CROSS APPLY to split each row and populate the new columns
• drop the old column that contains the multiple data elements
• upgrade your save and load routines to use the new columns
• never worry about splitting the data again!