如何扩大逗号分隔值到使用SQL Server 2005不同的行？如何扩大逗号分隔值到使用SQL Se

我有一个表，看起来像这样：

ProductId, Color
"1", "red, blue, green"
"2", null
"3", "purple, green"

我想将它扩展成：

ProductId, Color
1, red
1, blue
1, green
2, null
3, purple
3, green

请告诉我最简单的方法来做到这一点？ 是否有可能在没有一个进程的循环？

Answer 1:

看看这个功能。 我已经做了类似的伎俩分裂和甲骨文转数据。 环在数据插入解码值到一个临时表。 修道院的是，MS可以让你做到这一点上的苍蝇，而甲骨文需要一个明确的临时表。

MS SQL拆分功能
更好的分流功能

按作者编辑：这真是棒极了。 最终代码看起来像这样（创建分割功能后）：

select pv.productid, colortable.items as color
from product p 
    cross apply split(p.color, ',') as colortable

Answer 2:

根据你的表：

create table test_table
(
     ProductId  int
    ,Color      varchar(100)
)

insert into test_table values (1, 'red, blue, green')
insert into test_table values (2, null)
insert into test_table values (3, 'purple, green')

创建这样一个新表：

CREATE TABLE Numbers
(
    Number  int   not null primary key
)

具有包含值1到8000左右的行。

这将返回你想要什么：

编辑
这里是一个更好的查询，从@Christopher克莱恩伟大的答案略作修改：

我添加了“LTRIM（）”所以在颜色列表中的空间，将妥善处理：“红，蓝，绿”。 他的解决方案不要求空间“红，蓝，绿”。 另外，我宁愿用我自己的编号表，而不是使用master.dbo.spt_values，这允许移除一个派生表的了。

SELECT
    ProductId, LEFT(PartialColor, CHARINDEX(',', PartialColor + ',')-1) as SplitColor
    FROM (SELECT 
              t.ProductId, LTRIM(SUBSTRING(t.Color, n.Number, 200)) AS PartialColor
              FROM test_table             t
                  LEFT OUTER JOIN Numbers n ON n.Number<=LEN(t.Color) AND SUBSTRING(',' + t.Color, n.Number, 1) = ','
         ) t

编辑结束

SELECT
    ProductId, Color --,number
    FROM (SELECT
              ProductId
                  ,CASE
                       WHEN LEN(List2)>0 THEN LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(',', List2, number+1)-number - 1)))
                       ELSE NULL
                   END AS Color
                  ,Number
              FROM (
                       SELECT ProductId,',' + Color + ',' AS List2
                           FROM test_table
                   ) AS dt
                  LEFT OUTER JOIN Numbers n ON (n.Number < LEN(dt.List2)) OR (n.Number=1 AND dt.List2 IS NULL)
              WHERE SUBSTRING(List2, number, 1) = ',' OR List2 IS NULL
         ) dt2
    ORDER BY ProductId, Number, Color

这里是我的结果集：

ProductId   Color
----------- --------------
1           red
1           blue
1           green
2           NULL
3           purple
3           green

(6 row(s) affected)

这是你想要的顺序相同...

Answer 3:

你可以尝试了这一点，那并不需要任何额外的功能：

declare @t table (col1 varchar(10), col2 varchar(200))
insert @t
          select '1', 'red,blue,green'
union all select '2', NULL
union all select '3', 'green,purple'


select col1, left(d, charindex(',', d + ',')-1) as e from (
    select *, substring(col2, number, 200) as d from @t col1 left join
        (select distinct number from master.dbo.spt_values where number between 1 and 200) col2
        on substring(',' + col2, number, 1) = ',') t

Answer 4:

如果在所有可能解决您的数据库。 逗号分隔的数据库单元列表显示的时间或更多的有缺陷的模式99％。

Answer 5:

我会为此创建CLR表定义的函数：

http://msdn.microsoft.com/en-us/library/ms254508(VS.80).aspx

这样做的原因是CLR代码将是在相隔解析字符串（计算工作）好多了，可以回传给该信息作为一组，这是SQL Server是在（组管理）真的很好。

该CLR函数将返回基于解析值（和值的输入ID）的一系列记录。

这样，你会使用CROSS在表中的每个元素上应用。

Answer 6:

只要将您的列到XML和查询。 下面是一个例子。

select 
    a.value('.', 'varchar(42)') c
from (select cast('<r><a>' + replace(@CSV, ',', '</a><a>') + '</a></r>' as xml) x) t1
cross apply x.nodes('//r/a') t2(a)

文章来源: How do I expand comma separated values into separate rows using SQL Server 2005?