T-SQL Split string into many-to-one relationship?

问题:

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!

回答1:

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

回答2:

your split string function is really slow, here is a link to make a much faster one:

http://www.sommarskog.se/arrays-in-sql.html