Two Split String Merging into one table variable

Question

I have two string variable having value like

@id = '1#2#3#4#5'
@fid = '11#12#13#14#15'

in stored procedure i used table varrible with split string to make the table for both of the varrible

Select Value from STRING_SPLIT(@id,'#')
Select Value from STRING_SPLIT(@fid,'#')

this gives me two separate table with @id and @fid data.but i need to merge the data in one table. i need output look like

fid|id

11 |1
12 |2
13 |3
14 |4
15 |5

How can i do that?

But if i do like this select a.value,b.Value FROM STRING_SPLIT(@fid,'#') a,STRING_SPLIT(@id,'#') b

i get 25 rows instead of 5.each value of @id will associated with every value of @fid and i don't want this type of result.

any other alternative to do this?

score 1 · Accepted Answer · answered Jan 18 '18 at 11:20

SELECT    t1.ID
        , t2.FID
FROM (
        Select Value As ID
                , ROW_NUMBER() OVER (ORDER BY  Value) rn       
        from STRING_SPLIT(@id,'#')
    ) t1
INNER JOIN 
        (
           Select Value AS FID
                , ROW_NUMBER() OVER (ORDER BY  Value) rn 
           from STRING_SPLIT(@fid,'#')
        ) t2 ON t1.rn = t2.rn

Two Split String Merging into one table variable

1 Answers1