question

DonBaun avatar image
DonBaun asked

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 characters needed characters left characters exceeded

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

1 Answer

· Write an Answer
anthony.green avatar image
anthony.green answered

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 comment
10 |1200 characters needed characters left characters exceeded

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

Nicely done, Anthony!

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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