SQL Server 2005 - Select语句中的表函数

I have a table called PurchaseOrderDetail.

我有一个名为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,

我还有一个名为Split的函数,它接受一个分隔符和一个字符串并返回一个表,

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

所以从Split中调用select *(';','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

我需要为每个PurchaseOrderDetail分解这些信息并在报告中显示它们

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

是否可能,或者我是以错误的方式解决这个问题

2 个解决方案

#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:

您使用apply关键字“加入”表值函数。只需将字段传递给函数,而不是使用“ON”链接表达式。来自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.

OUTER APPLY相当于LEFT JOIN。

Edit

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

在您的示例中,您可以在应用函数后将“cn = 1”和“cn = 2”条件添加为WHERE子句。

#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!

表有多于1列的原因是有原因的。这是一个痛苦,但总是从一个共同的列中分离出来的价值!

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

更新并加入CROSS APPLY以拆分每一行并填充新列

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!

永远不要担心再次拆分数据!

#1