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

I have a table that looks like this:

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

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

And I want to expand it to this:

我想把它扩展到这个:

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

Whats the easiest way to accomplish this? Is it possible without a loop in a proc?

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

6 个解决方案

#1

Take a look at this function. I've done similar tricks to split and transpose data in Oracle. Loop over the data inserting the decoded values into a temp table. The convent thing is that MS will let you do this on the fly, while Oracle requires an explicit temp table.

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

MS SQL Split Function
Better Split Function

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

Edit by author: This worked great. Final code looked like this (after creating the split function):

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

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

#2

based on your tables:

根据您的表格:

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 a new table like this:

创建一个这样的新表:

CREATE TABLE Numbers
(
    Number  int   not null primary key
)

that has rows containing values 1 to 8000 or so.

包含值为1到8000左右的行。

this will return what you want:

这将返回你想要的:

EDIT
here is a much better query, slightly modified from the great answer from @Christopher Klein:

编辑这里是一个更好的查询,稍微修改了@Christopher Klein的好答案:

I added the "LTRIM()" so the spaces in the color list, would be handled properly: "red, blue, green". His solution requires no spaces "red,blue,green". Also, I prefer to use my own Number table and not use master.dbo.spt_values, this allows the removal of one derived table too.

我添加了“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

EDIT END

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

here is my result set:

这是我的结果集:

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

(6 row(s) affected)

which is the same order you want...

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

#3

You can try this out, doesnt require any additional functions:

你可以尝试这个,不需要任何额外的功能:

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

#4

Fix your database if at all possible. Comma delimited lists in database cells indicate a flawed schema 99% of the time or more.

尽可能修复您的数据库。数据库单元格中逗号分隔的列表表明99%的时间或更多时间存在缺陷模式。

#5

I would create a CLR table-defined function for this:

我会为此创建一个CLR表定义的函数:

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

The reason for this is that CLR code is going to be much better at parsing apart the strings (computational work) and can pass that information back as a set, which is what SQL Server is really good at (set management).

这样做的原因是CLR代码在解析字符串(计算工作)方面要好得多,并且可以将这些信息作为一个集传递回来,这正是SQL Server真正擅长的(集合管理)。

The CLR function would return a series of records based on the parsed values (and the input id value).

CLR函数将根据解析的值(和输入id值)返回一系列记录。

You would then use a CROSS APPLY on each element in your table.

然后,您将对表中的每个元素使用CROSS APPLY。

#6

Just convert your columns into xml and query it. Here's an example.

只需将列转换为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)

#1