将逗号分隔的字符串转换为单独的行

Question

将逗号分隔的字符串转换为单独的行我有一个像这样的SQL表：| SomeID         | OtherID     | Data+----------------+-------------+-------------------| abcdef-.....   | cdef123-... | 18,20,22| abcdef-.....   | 4554a24-... | 17,19| 987654-.....   | 12324a2-... | 13,19,20是否有一个查询，我可以执行查询，如SELECT OtherID, SplitData WHERE SomeID = 'abcdef-.......'返回单个行，如下所示：| OtherID     | SplitData+-------------+-------------------| cdef123-... | 18| cdef123-... | 20| cdef123-... | 22| 4554a24-... | 17| 4554a24-... | 19基本上将逗号中的数据拆分成单独的行？我知道将comma-separated字符串存储到关系数据库听起来很愚蠢，但消费者应用程序中的正常用例使得这非常有用。我不想在应用程序中进行拆分，因为我需要分页，因此我想在重构整个应用程序之前探索选项。这是SQL Server 2008（非R2）。

Answer 1

您可以使用SQL Server中的精彩递归函数：

样品表：

CREATE TABLE Testdata

(

    SomeID INT,

    OtherID INT,

    String VARCHAR(MAX)

)

INSERT Testdata SELECT 1,  9, '18,20,22'

INSERT Testdata SELECT 2,  8, '17,19'

INSERT Testdata SELECT 3,  7, '13,19,20'

INSERT Testdata SELECT 4,  6, ''

INSERT Testdata SELECT 9, 11, '1,2,3,4'

查询

;WITH tmp(SomeID, OtherID, DataItem, String) AS

(

    SELECT

        SomeID,

        OtherID,

        LEFT(String, CHARINDEX(',', String + ',') - 1),

        STUFF(String, 1, CHARINDEX(',', String + ','), '')

    FROM Testdata

    UNION all

    SELECT

        SomeID,

        OtherID,

        LEFT(String, CHARINDEX(',', String + ',') - 1),

        STUFF(String, 1, CHARINDEX(',', String + ','), '')

    FROM tmp

    WHERE

        String > ''

)

SELECT

    SomeID,

    OtherID,

    DataItem

FROM tmp

ORDER BY SomeID

-- OPTION (maxrecursion 0)

-- normally recursion is limited to 100. If you know you have very long

-- strings, uncomment the option

产量

 SomeID | OtherID | DataItem 

--------+---------+----------

 1      | 9       | 18       

 1      | 9       | 20       

 1      | 9       | 22       

 2      | 8       | 17       

 2      | 8       | 19       

 3      | 7       | 13       

 3      | 7       | 19       

 3      | 7       | 20       

 4      | 6       |          

 9      | 11      | 1        

 9      | 11      | 2        

 9      | 11      | 3        

 9      | 11      | 4        

Answer 2

检查一下

 SELECT A.OtherID,  
     Split.a.value('.', 'VARCHAR(100)') AS Data  
 FROM  
 (
     SELECT OtherID,  
         CAST ('<M>' + REPLACE(Data, ',', '</M><M>') + '</M>' AS XML) AS Data  
     FROM  Table1 ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a);