CODEWITHSUNDEEP
sql-serversql-server-2008-r2
Naim Halai
Naim Halai

Reputation: 375

Multiple comma separated string columns to multiple rows

I have a scenario where I have data like below in #Testdata temp table.

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

Upvotes: 1

Views: 66

Answers (2)

iamdave
iamdave

Reputation: 12243

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

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;

Upvotes: 2

James Z
James Z

Reputation: 12317

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.

Upvotes: 2

Related Questions