SQL: Parsing an associative input string with three delimiters

Question

I can do this using cursors, but I'm trying to avoid it if at all possible. Here's a bit of sample code that I've got going:

declare @string varchar(max) = 'person1:item1~item2~item3~etc^person2:item1~item2~item3~etc'

declare @table1 table (id int, value varchar(500))
declare @table2 table (id varchar(50), value varchar(500))
declare @table3 table (id varchar(50), value varchar(50))

insert @table1 (id, value) select * from fn_ParseDelimitedStrings(@string, '^')

insert @table2 (id, value) 
    select 
        id = (select f.value from fn_ParseDelimitedStrings(t.value, ':') f where f.RowId=1),
        value = (select f.value from fn_ParseDelimitedStrings(t.value, ':') f where f.RowId=2)
    from @table1 t

select * from @table2

The above code gives me the data in the form of:

id        value
-------------------------------
person1   item1~item2~item3~etc
person2   item1~item2~item3~etc

But I need the data in this form:

id        value
-------------------------------
person1   item1
person1   item2
person1   item3
person1   etc
person2   item1
person2   item2
person2   item3
person3   etc

The input string can have any number of "persons", and each person can have any number of "items".

fn_ParseDelimitedStrings is a custom function we have that returns a table of index-value pairs for each delimited item. ie:

RowID   Value
-------------
1       item1
2       item2
3       item3
4       etc

I'm having trouble associating each "item" from the final split to the "person" that they should be associated with.

Is there anything that can be done or am I going to have to use a cursor?

Check out this post . . . http://stackoverflow.com/questions/697519/split-function-equivalent-in-tsql. If you care about performance, look at what Aaron Bertrand has to say. — Gordon Linoff, Aug 08 '12 at 17:36

score 2 · Accepted Answer · edited Aug 08 '12 at 17:47

outer apply will join current row with all the rows found in derived table inside outer apply:

insert @table2 (id, value) 
select 
    id = (select f.value from fn_ParseDelimitedStrings(t.value, ':') f where f.RowId=1),
    value = v1.value
from @table1 t
outer apply
(
  select v.value
    from fn_ParseDelimitedStrings(
         (select f.value 
            from fn_ParseDelimitedStrings(t.value, ':') f 
           where f.RowId=2)
         , '~') v 
) v1

*Edited value1 to v1 to match the outermost select

SQL: Parsing an associative input string with three delimiters

1 Answers1