Estou tentando dividir duas colunas com strings delimitadas em linhas. As posições dos valores em cada string estão relacionadas, então estou tentando dividi-lo para que os valores relacionados estejam em uma linha. Não consigo usar a função porque não consigo criar objetos no banco de dados
Aqui está a tabela e os dados de amostra
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');
e minha saída desejada seria
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
Consegui que a consulta funcionasse se eu dividisse apenas uma coluna, então defino dois CTEs com row_number e uno em ID e row_number. Isso fornece a saída desejada, mas minha tabela ao vivo é muito grande e eu esperava uma maneira de passar pela tabela apenas uma vez, em vez de duas.
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('<Keys><key>'+REPLACE(keys, ',', '</key><key>')+'</key></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('<vals><val>'+REPLACE(vals, ',', '</val><val>')+'</val></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;
