I have a scenario where I have data like below in #Testdata temp table.
我有一个场景,在#Testdata临时表中有如下数据。
create table #Testdata(SomeID int, OtherID int, Data varchar(max), Data2 VARCHAR(MAX))
insert #Testdata select 1, 9, '18,20,22', '12,13'
insert #Testdata select 2, 8, '17,19', ''
insert #Testdata select 3, 7, '13,19,20', '14,,50'
insert #Testdata select 4, 6, '', ''
Now I require to show result as below.
现在我需要显示如下结果。
|SomeId|OtherID|Data|Data2|
|1 |9 |18 |12 |
|1 |9 |20 |13 |
|1 |9 |22 | |
|2 |8 |17 | |
|2 |8 |19 | |
|3 |7 |13 |14 |
|3 |7 |19 | |
|3 |7 |10 |50 |
|4 |6 | | |
Any responses will be greatly appreciated.
我们将非常感谢您的回复。
Thanks
谢谢
2 个解决方案
#1
2
Using Jeff Moden's string split function you can do the following to get the output you need, though I am sure there is a more efficient way...
使用Jeff Moden的string split函数,您可以执行以下操作以获得所需的输出,不过我确信有一种更有效的方法……
declare @Testdata table(SomeID int, OtherID int, Data varchar(max), Data2 VARCHAR(MAX));
insert @Testdata select 1, 9, '18,20,22', '12,13';
insert @Testdata select 2, 8, '17,19', '';
insert @Testdata select 3, 7, '13,19,20', '14,,50';
insert @Testdata select 4, 6, '', '1,2,3,4,5';
with t1 as
(
select t.SomeID
,t.OtherID
,t1.ItemNumber
,t1.Item as Data1
from @Testdata t
outer apply dbo.DelimitedSplit8K(t.Data,',') t1
), t2 as
(
select t.SomeID
,t.OtherID
,t2.ItemNumber
,t2.Item as Data2
from @Testdata t
outer apply dbo.DelimitedSplit8K(t.Data2,',') t2
)
select isnull(t1.SomeID,t2.SomeID) as SomeID
,isnull(t1.OtherID,t2.OtherID) as OtherID
,t1.Data1
,t2.Data2
from t1
full join t2
on t1.SomeID = t2.SomeID
and t1.ItemNumber = t2.ItemNumber;
#2
2
You could for example install the DelimitedSplit8k by Jeff Moden. The result set will contain both the item and item number, so with the item number you can join the 2 strings you split together.
例如,可以安装Jeff Moden设计的DelimitedSplit8k。结果集将包含项目号和项目号,因此您可以使用项目号加入您分割的两个字符串。
#1
2
Using Jeff Moden's string split function you can do the following to get the output you need, though I am sure there is a more efficient way...
使用Jeff Moden的string split函数,您可以执行以下操作以获得所需的输出,不过我确信有一种更有效的方法……
declare @Testdata table(SomeID int, OtherID int, Data varchar(max), Data2 VARCHAR(MAX));
insert @Testdata select 1, 9, '18,20,22', '12,13';
insert @Testdata select 2, 8, '17,19', '';
insert @Testdata select 3, 7, '13,19,20', '14,,50';
insert @Testdata select 4, 6, '', '1,2,3,4,5';
with t1 as
(
select t.SomeID
,t.OtherID
,t1.ItemNumber
,t1.Item as Data1
from @Testdata t
outer apply dbo.DelimitedSplit8K(t.Data,',') t1
), t2 as
(
select t.SomeID
,t.OtherID
,t2.ItemNumber
,t2.Item as Data2
from @Testdata t
outer apply dbo.DelimitedSplit8K(t.Data2,',') t2
)
select isnull(t1.SomeID,t2.SomeID) as SomeID
,isnull(t1.OtherID,t2.OtherID) as OtherID
,t1.Data1
,t2.Data2
from t1
full join t2
on t1.SomeID = t2.SomeID
and t1.ItemNumber = t2.ItemNumber;
#2
2
You could for example install the DelimitedSplit8k by Jeff Moden. The result set will contain both the item and item number, so with the item number you can join the 2 strings you split together.
例如,可以安装Jeff Moden设计的DelimitedSplit8k。结果集将包含项目号和项目号,因此您可以使用项目号加入您分割的两个字符串。