如何使用SQL Server 2005将逗号分隔值扩展为单独的行？

我有一个看起来像这样的表:

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

什么是最简单的方法来实现这一目标？是否可以在proc中没有循环？

1> chilltemp..：

---

看看这个功能.我在Oracle中分割和转置数据时也做了类似的技巧.循环数据,将解码后的值插入临时表.修道院的事情是MS会让你动态地执行此操作,而Oracle需要一个显式的临时表.

MS SQL分割功能
更好的分割功能

作者编辑: 这很有效.最终代码看起来像这样(在创建split函数之后):

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

---

那些循环会很慢,我敢打赌,我的查询会在任何时候吸烟...

---

对于SQL 2016，您可以使用内置函数：`CROSS APPLY STRING_SPLIT（p.color，'，'）`

2> KM...：

---

根据您的表格:

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 Klein的好答案:

我添加了"LTRIM()",因此颜色列表中的空格将被正确处理:"红色,蓝色,绿色".他的解决方案不需要"红色,蓝色,绿色"空间.另外,我更喜欢使用自己的Number表而不使用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)

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