如何使用 SQL Server 2005 将逗号分隔的值扩展为单独的行? [英] How do I expand comma separated values into separate rows using SQL Server 2005?

问题描述

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

I have a table that looks like this:

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 中可以没有循环吗?

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

推荐答案

我在帖子发布 10 年后才提出这个问题.SQL Server 2016 添加了 STRING_SPLIT 函数.通过使用它，这可以写成如下.

I arrived this question 10 years after the post. SQL server 2016 added STRING_SPLIT function. By using that, this can be written as below.

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;

这篇关于如何使用 SQL Server 2005 将逗号分隔的值扩展为单独的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！