Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

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

Visualizações: 10.683 views
Esse post é a parte 5 de 5 da série String Split
Tempo de Leitura: 6 minutos

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:

Resultado:

Uma das grandes reclamações dos usuários sobre a função STRING_SPLIT é a falta de um campo numérico e incremental retornando o índice de cada linha que foi quebrada. No exemplo acima, eu tive que criar essa linha utilizando uma função de ranking (row_number), mas fique atento que isso NÃO garante a ordenação correta.

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:

Resultado:

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:

Resultado:

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:

Resultado:

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

Resultado:

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!