Split two delimited strings in same order without function

I am trying to split two columns with delimited strings into rows. The positions of the values in each string are related so I am trying to split it out so that the related values are in a row. I am unable to use function as I cannot create objects in the database Here is sample table and data CREATE TABLE #temp (id INT, keys VARCHAR(50), vals VARCHAR(50) ); INSERT INTO #temp VALUES (1, '1,2,3', 'one,two,three'), (2, '4,5,6', 'four,five,six'), (3, '7,8,9', 'seven,eight,nine'); and my desired output would be ID key val 1 1 one 1 2 two 1 3 three 2 4 four 2 5 five 2 6 six 3 7 seven 3 8 eight 3 9 nine I got the query to work if I only split one column, so I define two CTEs with row_number and join on ID and row_number. This does give desired output but my live table is very large and I was hoping for a way to pass through the table only once, instead of twice. with keys as( SELECT id,keys,vals, keys.keyid.value('.', 'VARCHAR(8000)') AS keyid, row_number() over(order by (select null)) as rn FROM (SELECT id,keys,vals, CAST(''+REPLACE(keys, ',', '')+'' AS XML) AS tempkeys FROM #temp ) AS temp CROSS APPLY tempkeys.nodes('/Keys/key') AS keys(keyid)), vals as( SELECT id,keys,vals, vals.val.value('.', 'VARCHAR(8000)') AS valid, row_number() over(order by (select null)) as rn FROM (SELECT id,keys,vals, CAST(''+REPLACE(vals, ',', '')+'' AS XML) AS tempvals FROM #temp ) AS temp CROSS APPLY tempvals.nodes('/vals/val') AS vals(val)) SELECT k.id, k.keyid, v.valid FROM keys AS k INNER JOIN vals AS v ON k.id = v.id AND k.rn = v.rn;