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

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,    DataItemFROM tmpORDER 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

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