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;
Crie a função em
msdb
ou em outro lugar.Em seguida, como @gbn observou, faça referência a ele pelo nome de 3 partes sempre que sua consulta tiver que ser executada.
Resultados:
O plano resultante, mostrado no Plan Explorer (disclaimer: I'm the Product Manager) , não é a coisa mais bonita que já vi ( clique para ampliar um pouco ):
Mas há exatamente uma varredura de #temp (custo de 4%). Os maiores custos são dois tipos e um carretel, e há alguma E/S devido a uma mesa de trabalho que não tenho certeza se é evitável.
Se você SABE que terá apenas 50 caracteres em qualquer uma dessas strings, então você poderá obter um plano muito mais simples com uma
Numbers
tabela embutida (as pessoas se opõem a isso, mas são muito úteis e estão quase sempre na memória se você as referenciar o suficiente). Isso não ajuda na E/S, mas remover o CTE recursivo e outras construções de construir os números dentro da função é bastante útil para a CPU etc.Primeiro, a tabela de números:
Em seguida, uma segunda versão da função:
Aqui está o plano mais simples que resulta (novamente, clique para ampliar ):
O plano ainda tem duas operações de classificação, mas o spool se foi, ainda há apenas uma varredura de
#temp
e, em meus testes limitados, os números de custo (números de custo absolutos, não %) foram sempre melhores.Eu não sei exatamente qualquer uma delas será dimensionada com muito mais linhas, mas vale a pena testar, e se você comparar isso com outras soluções e não puder dimensionar bem, talvez seja um ponto que você reconsidere o design (armazene essas relacionalmente em vez de como conjuntos separados por vírgulas).
Eu entrei no mesmo problema com um grande número de linhas e quatro colunas de lista.
As soluções anteriores não funcionam para mim.
A solução de @AaronBertrand tem um problema com um número diferente de elementos na lista. O problema pode ser resolvido adicionando uma partição no ROW_NUMBER:
No entanto, ainda não treino para mim por causa do meu grande número de linhas e elementos.
Eu criei o seguinte script para resolver meu problema sem usar uma função:
Resultados:
Plano de consulta:
Como você pode ver, o plano de consulta é muito simples e possui apenas uma varredura de tabela em #temp.
A solução também é bastante escalável.