T-SQL将字符串拆分成多对一关系？ [英] T-SQL Split string into many-to-one relationship?

问题描述

我有以下SQL脚本：

  DECLARE @temp表（
 ID int IDENTITY ），
 data nvarchar（100）
）
 
 INSERT INTO @temp（data）VALUES（'a，b，c'）
 INSERT INTO @temp data）VALUES（'d，e，f'）
 
 SELECT * 
 FROM @temp AS T 
 INNER JOIN 
（SELECT * 
 FROM dbo .__ StringSplit（T.data，'，'，T.ID））AS S 
 ON T.ID = S.RefID 
  

然后点击！执行，我得到这些：

 行17多部分标识符T.data不能绑定。 
  

我也试过非加入版本，并得到相同的错误：

  SELECT T.ID，S.Item AS dataItem 
 FROM @temp AS T，dbo .__ StringSplit（T.data，'，'， T.ID）AS S 
 WHERE T.ID = S.RefID 
  

...

我想要的是，我应该得到一个表，ID来自@ test.ID，@ test.data中的每个逗号分隔的值被分割

如何实现？

我需要使用

我在 http://pastebin.com/f7dd6350f

谢谢！

解决方案

在SQL2000中，您需要光标。在SQL2005 / 2008中，可以使用CROSS APPLY satement;可能像下一个（现在不能测试）：

  SELECT T.ID，S.Item AS dataItem 
 FROM @temp AS T CROSS APPLY dbo .__ StringSplit（T.data，'，'，T.ID）AS S 
  

$ b b

---

EDIT - 我发现此页面上CROSS APPLY ，然后想出：

  SELECT T.ID，S.Item AS dataItem 
 FROM @temp AS T 
 CROSS APPLY 
 dbo .__ StringSplit（T.data，'，'，T.ID）AS S 
 WHERE T.ID = S.RefID 
  

这解决了我的问题： - ） p>

I have the following SQL script:

DECLARE @temp table (
    ID int IDENTITY(1, 1),
    data nvarchar(100)
)

INSERT INTO @temp (data) VALUES ('a,b,c')
INSERT INTO @temp (data) VALUES ('d,e,f')

SELECT *
FROM @temp AS T
    INNER JOIN
        (SELECT *
        FROM dbo.__StringSplit(T.data, ',', T.ID)) AS S
    ON T.ID = S.RefID

And on after clicking !Execute, I got these:

Line 17 The multi-part identifier "T.data" could not be bound.

I have also tried the non-join version and got the same error:

SELECT T.ID, S.Item AS dataItem
FROM @temp AS T, dbo.__StringSplit(T.data, ',', T.ID) AS S
WHERE T.ID = S.RefID

...

What I expected was that I should gets a table with IDs coming from @test.ID and each comma-separated values in @test.data gets split up into its own records and its value put into the dataItem field.

How can I accomplish that?
Am I required to use cursors?

I've pasted the dbo.__StringSplit table-valued-function implementation at http://pastebin.com/f7dd6350f

Thanks!

解决方案

In SQL2000 you need cursors. In SQL2005/2008, you can use CROSS APPLY satement; probably like next (can't test just now):

SELECT T.ID, S.Item AS dataItem
FROM @temp AS T CROSS APPLY dbo.__StringSplit(T.data, ',', T.ID) AS S

---

EDIT - I found this page on CROSS APPLY and then came up with:

SELECT T.ID, S.Item AS dataItem
FROM @temp AS T
    CROSS APPLY
    dbo.__StringSplit(T.data, ',', T.ID) AS S
WHERE T.ID = S.RefID

Which solved my problem :-)

这篇关于T-SQL将字符串拆分成多对一关系？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！