split TWO delimited strings into rows but keep the two sets of strings together in each row.

Question

question

DonBaun asked Apr 14, '21

split TWO delimited strings into rows but keep the two sets of strings together in each row.

Each record has an identifier, and TWO fields with delimited strings that contain varying numbers (sometimes a single set, could be dozens of sets) of related data:

RecordID Item_List Item_Values

RecID1 Item1|Item2|Item3|Item4 Val1|Val2|Val3|Val4

I need to end up with:

RecID1 Item1 Val1

RecID1 Item2 Val2

RecID1 Item3 Val3

RecID1 Item4 Val4

I can easily split out ONE column into multiple rows, but how do I do this with TWO columns, keeping the sets of items together? Thanks for your consideration!

string-splitting

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2021-04-14T13:39:50Z

anthony.green answered Apr 14, '21

Use something like dbo.delimitedsplit8k (https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%e2%80%9ccsv-splitter%e2%80%9d-function) and do something like this

if object_id('tempdb.dbo.#recs') is not null
begin
    drop table #recs
end
go
create table #recs (RecordID varchar(10), Item_List varchar(max), Item_Values varchar(max))
insert into #recs values
('RecID1','Item1|Item2|Item3|Item4','Val1|Val2|Val3|Val4'),
('RecID2','Item1|Item11|Item21|Item31','Val1|Val11|Val21|Val31')
select r.recordid, i.item as ItemFromList, v.item as ValueFromList
from #recs r
cross apply dbo.DelimitedSplit8K(r.item_list,'|') as i
cross apply  dbo.DelimitedSplit8K(r.item_values,'|') as v
where i.ItemNumber = v.ItemNumber

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jeff Moden commented · Apr 20, 2021 at 12:44 PM

Nicely done, Anthony!

0 ·

question