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
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)