如何使用 SQL Server 2005 将逗号分隔值扩展为单独的行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/702968/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I expand comma separated values into separate rows using SQL Server 2005?
提问by TheSoftwareJedi
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 中没有循环是否可能？
采纳答案by Hiroshi
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.
我在帖子发布 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;
回答by chilltemp
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
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
回答by KM.
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...
这是您想要的相同顺序...
回答by Christopher Klein
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
回答by Joel Coehoorn
Fix your database if at all possible. Comma delimited lists in database cells indicate a flawed schema 99% of the time or more.
如果可能，请修复您的数据库。数据库单元格中的逗号分隔列表在 99% 或更多的情况下表示有缺陷的模式。
回答by casperOne
I would create a CLR table-defined function for this:
我将为此创建一个 CLR 表定义函数：
http://msdn.microsoft.com/en-us/library/ms254508(VS.80).aspx
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。
回答by nurettin
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)
