SQL Server – Utilizando a STRING_SPLIT para transformar strings de uma linha em colunas

Fala pessoal!!
Nesse artigo eu gostaria de trazer um cenário que de vez em quando me perguntam sobre como resolver, que é transformar strings de uma linha para colunas, muito utilizado para contas contábeis ou centro de custo, por exemplo, que foi exatamente a situação que me pediram ajuda há uns 10 dias e resolvi tirar um tempinho para escrever esse artigo.

Exemplo de tabela:

E aí a pessoa precisava quebrar os números dos centros de custos (separados por “.”) em colunas diferentes, para conseguir montar uma hierarquia dos centros de custos e realizar algumas análises/montar relatório em cima desses dados.

Solução #1 – STRING_SPLIT

Caso você esteja utilizando a versão 2016+ do SQL Server, pode utiliar a função STRING_SPLIT para atingir ao objetivo desejado:

Result:

Para converter essas várias linhas em colunas, podemos utilizar funções de agregação com CASE’s, supondo que o nível máximo seria de 10 níveis:

Result:

E caso você queira utilizar uma solução mais dinâmica, onde o número de níveis da tabela seja definido pelo número de níveis dos dados reais, pode utilizar também o código abaixo:

Result:

Solução #2 – fncSplitTexto

Caso você esteja utilizando uma versão anterior à 2016 do SQL Server, a função STRING_SPLIT não poderá ser utilizada. Entretanto, você pode muito bem utilizar a função fncSplitTexto, que disponibilizei no artigo Como quebrar um string em uma tabela de substrings utilizando um delimitador no SQL Server.

A vantagem do uso dessa função, além da retrocompatibilidade, é que ela já possui o indexador por padrão.

Uma vez criada, a sua utilização é quase idêntica ao uso da STRING_SPLIT:

Result:

E para ler os dados dinamicamente, de acordo com as colunas existentes nos dados, você pode utilizar o código abaixo:

Result:

A performance das duas funções

Se você já estudou um pouco a parte de Performance Tuning ou já leu o meu artigo SQL Server – Comparação de performance entre Scalar Function e CLR Scalar Function, tenho certeza que você já sabe que a utilização da STRING_SPLIT é bem mais rápido que utilizar uma UDF (User Defined Function), mesmo que TVF (Table-valued Function).

Mesmo assim, vou mostrar a diferença de performance da função nativa (vou testar com e sem o ROW_NUMBER) com uma função UDF, num volume de dados de aproximadamente 20 mil registros:

Como vocês puderam ver, a diferença de performance é gigantesca entre uma UDF e uma função nativa, e quanto mais dados na tabela, maior é a diferença. Por este motivo, recomendo utilizar sempre a função nativa STRING_SPLIT, quando possível.

Bom pessoal, espero que vocês tenham gostado desse artigo.
Um grande abraço e até o próximo!