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

看看这个函数。我在Oracle中做过类似的拆分和转置数据的技巧。循环数据,将解码值插入临时表。修道院的事情是,MS会让你在运行中做到这一点,而Oracle需要一个显式的临时表。

MS SQL Split Function
Better Split Function

作者编辑: 这工作得很好。最终代码看起来像这样(在创建split函数后):

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

根据您的表格:

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
)

这将返回您想要的内容:

编辑
这里有一个更好的查询,与@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)

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

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

通过使用它,可以写成如下。

declare @product table
(
    ProductId int,
    Color     varchar(max)
);
insert into @product values (1, 'red, blue, green');
insert into @product values (2, null);
insert into @product values (3, 'purple, green');

select
    p.ProductId as ProductId,
    ltrim(split_table.value) as Color
from @product p
outer apply string_split(p.Color, ',') as split_table;

如果可能的话,修复你的数据库。数据库单元格中以逗号分隔的列表在99%或更多情况下表示架构有缺陷。

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

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

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

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)