请看一下这个函数。我在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
)

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

这将返回您想要的内容：

编辑

以下是一个更好的查询，从@Christopher Klein的精彩回答中稍作修改：

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

这和你想要的顺序一样。

你可以试一下，不需要任何额外的功能：

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

这个问题是我在发帖10年后提出的。SQL SERVER 2016新增了STRING_SPLIT函数。通过使用这一点，可以编写如下代码。

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%或更多的情况下表示模式有缺陷。

我将为此创建一个CLR表定义函数：

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

这样做的原因是，CLR代码将更好地解析字符串(计算工作)，并可以将该信息作为集合传回，这正是SQL Server真正擅长的(集合管理)。

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

然后，您可以在表中的每个元素上使用交叉应用。

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

为什么不使用动态SQL来达到这个目的，就像这样(根据您的需要进行调整)：

DECLARE @dynSQL VARCHAR(max)
SET @dynSQL = 'insert into DestinationTable(field) values'
select @dynSQL = @dynSQL + '('+ REPLACE(Color,',',''',''') + '),' from Table
SET @dynSql = LEFT(@dynSql,LEN(@dynSql) -1) -- delete the last comma
exec @dynSql

一个优点是您可以在任何SQL Server版本上使用它