- SQL Server – Quebrando strings em sub-strings utilizando separador (Split string)
- SQL Server – Função table-valued para quebrar uma string em linhas com tamanho de até N caracteres
- Como quebrar um string em uma tabela de substrings utilizando um delimitador no SQL Server
- SQL Server – charindexada: Uma função diferente para quebrar strings delimitadas (split)
- 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:
|
1 2 3 4 5 6 7 8 9 10 11 |
DECLARE @Tabela TABLE ( Conta VARCHAR(100) ) INSERT INTO @Tabela VALUES ('1'), ('01.04'), ('01.06'), ('01.04.14'), ('01.04.18'), ('1.1.3'), ('1.5.6'), ('1.1.2'), ('1.4.3.1.4'), ('1.6.4.32') SELECT *, ROW_NUMBER() OVER(PARTITION BY A.Conta ORDER BY A.Conta) AS Nivel FROM @Tabela A CROSS APPLY STRING_SPLIT(A.Conta, '.') |
Resultado:
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
DECLARE @Tabela TABLE ( Conta VARCHAR(100) ) INSERT INTO @Tabela VALUES ('1'), ('01.04'), ('01.06'), ('01.04.14'), ('01.04.18'), ('1.1.3'), ('1.5.6'), ('1.1.2'), ('1.4.3.1.4'), ('1.6.4.32') IF (OBJECT_ID('tempdb..#Tabela') IS NOT NULL) DROP TABLE #Tabela ;WITH dados AS ( SELECT A.Conta, [value] AS Palavra, ROW_NUMBER() OVER(PARTITION BY A.Conta ORDER BY A.Conta) AS Nivel FROM @Tabela A CROSS APPLY STRING_SPLIT(A.Conta, '.') ) SELECT A.Conta, MAX(CASE WHEN A.Nivel = 1 THEN CONVERT(INT, A.Palavra) END) AS Parte1, MAX(CASE WHEN A.Nivel = 2 THEN CONVERT(INT, A.Palavra) END) AS Parte2, MAX(CASE WHEN A.Nivel = 3 THEN CONVERT(INT, A.Palavra) END) AS Parte3, MAX(CASE WHEN A.Nivel = 4 THEN CONVERT(INT, A.Palavra) END) AS Parte4, MAX(CASE WHEN A.Nivel = 5 THEN CONVERT(INT, A.Palavra) END) AS Parte5, MAX(CASE WHEN A.Nivel = 6 THEN CONVERT(INT, A.Palavra) END) AS Parte6, MAX(CASE WHEN A.Nivel = 7 THEN CONVERT(INT, A.Palavra) END) AS Parte7, MAX(CASE WHEN A.Nivel = 8 THEN CONVERT(INT, A.Palavra) END) AS Parte8, MAX(CASE WHEN A.Nivel = 9 THEN CONVERT(INT, A.Palavra) END) AS Parte9, MAX(CASE WHEN A.Nivel = 10 THEN CONVERT(INT, A.Palavra) END) AS Parte10 FROM dados A GROUP BY A.Conta |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 |
------------------------------------------------------ -- GERA OS DADOS ------------------------------------------------------ DECLARE @Tabela TABLE ( Conta VARCHAR(100) ) INSERT INTO @Tabela VALUES ('1'), ('01.04'), ('01.06'), ('01.04.14'), ('01.04.18'), ('1.1.3'), ('1.5.6'), ('1.1.2'), ('1.4.3.1.4'), ('1.6.4.32') ------------------------------------------------------ -- QUEBRA AS CONTAS EM PARTES ------------------------------------------------------ IF (OBJECT_ID('tempdb..#Partes') IS NOT NULL) DROP TABLE #Partes SELECT A.Conta, CONVERT(INT, [value]) AS Parte, ROW_NUMBER() OVER(PARTITION BY A.Conta ORDER BY A.Conta) AS Nivel INTO #Partes FROM @Tabela A CROSS APPLY STRING_SPLIT(A.Conta, '.') ------------------------------------------------------ -- GERA OS SCRIPTS PRA CRIAÇÃO E ATUALIZAÇÃO DINÂMICA ------------------------------------------------------ DECLARE @Contador INT = 1, @Total INT = (SELECT MAX(Nivel) FROM #Partes), @QueryCreate VARCHAR(MAX) = 'CREATE TABLE ##Tabela ( Conta VARCHAR(100)', @QueryUpdate VARCHAR(MAX) = '' WHILE(@Contador <= @Total) BEGIN SET @QueryCreate += ', Nivel' + CONVERT(VARCHAR(10), @Contador) + ' INT' SET @QueryUpdate += 'UPDATE A SET A.Nivel' + CONVERT(VARCHAR(10), @Contador) + ' = B.Parte FROM ##Tabela A JOIN #Partes B ON A.Conta = B.Conta WHERE B.Nivel = ' + CONVERT(VARCHAR(10), @Contador) + '; ' SET @Contador += 1 END SET @QueryCreate += ' )' ------------------------------------------------------ -- CRIA A TABELA DINAMICAMENTE ------------------------------------------------------ IF (OBJECT_ID('tempdb..##Tabela') IS NOT NULL) DROP TABLE ##Tabela EXEC(@QueryCreate) INSERT INTO ##Tabela ( Conta ) SELECT DISTINCT Conta FROM #Partes ------------------------------------------------------ -- ATUALIZA OS DADOS DINAMICAMENTE ------------------------------------------------------ EXEC(@QueryUpdate) ------------------------------------------------------ -- RESULTADO FINAL ------------------------------------------------------ SELECT * FROM ##Tabela |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
DECLARE @Tabela TABLE ( Conta VARCHAR(100) ) INSERT INTO @Tabela VALUES ('1'), ('01.04'), ('01.06'), ('01.04.14'), ('01.04.18'), ('1.1.3'), ('1.5.6'), ('1.1.2'), ('1.4.3.1.4'), ('1.6.4.32') IF (OBJECT_ID('tempdb..#Tabela') IS NOT NULL) DROP TABLE #Tabela SELECT Conta, MAX(CASE WHEN B.Id = 1 THEN CONVERT(INT, B.Palavra) END) AS Parte1, MAX(CASE WHEN B.Id = 2 THEN CONVERT(INT, B.Palavra) END) AS Parte2, MAX(CASE WHEN B.Id = 3 THEN CONVERT(INT, B.Palavra) END) AS Parte3, MAX(CASE WHEN B.Id = 4 THEN CONVERT(INT, B.Palavra) END) AS Parte4, MAX(CASE WHEN B.Id = 5 THEN CONVERT(INT, B.Palavra) END) AS Parte5, MAX(CASE WHEN B.Id = 6 THEN CONVERT(INT, B.Palavra) END) AS Parte6, MAX(CASE WHEN B.Id = 7 THEN CONVERT(INT, B.Palavra) END) AS Parte7, MAX(CASE WHEN B.Id = 8 THEN CONVERT(INT, B.Palavra) END) AS Parte8, MAX(CASE WHEN B.Id = 9 THEN CONVERT(INT, B.Palavra) END) AS Parte9, MAX(CASE WHEN B.Id = 10 THEN CONVERT(INT, B.Palavra) END) AS Parte10 FROM @Tabela CROSS APPLY dbo.fncSplitTexto(Conta, '.') AS B GROUP BY Conta |
Resultado:
E para ler os dados dinamicamente, de acordo com as colunas existentes nos dados, você pode utilizar o código abaixo:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 |
------------------------------------------------------ -- GERA OS DADOS ------------------------------------------------------ DECLARE @Tabela TABLE ( Conta VARCHAR(100) ) INSERT INTO @Tabela VALUES ('1'), ('01.04'), ('01.06'), ('01.04.14'), ('01.04.18'), ('1.1.3'), ('1.5.6'), ('1.1.2'), ('1.4.3.1.4'), ('1.6.4.32') ------------------------------------------------------ -- QUEBRA AS CONTAS EM PARTES ------------------------------------------------------ IF (OBJECT_ID('tempdb..#Partes') IS NOT NULL) DROP TABLE #Partes SELECT Conta, B.Id, CONVERT(INT, B.Palavra) AS Parte INTO #Partes FROM @Tabela CROSS APPLY dbo.fncSplitTexto(Conta, '.') AS B ------------------------------------------------------ -- GERA OS SCRIPTS PRA CRIAÇÃO E ATUALIZAÇÃO DINÂMICA ------------------------------------------------------ DECLARE @Contador INT = 1, @Total INT = (SELECT MAX(Id) FROM #Partes), @QueryCreate VARCHAR(MAX) = 'CREATE TABLE ##Tabela ( Conta VARCHAR(100)', @QueryUpdate VARCHAR(MAX) = '' WHILE(@Contador <= @Total) BEGIN SET @QueryCreate += ', Parte' + CONVERT(VARCHAR(10), @Contador) + ' INT' SET @QueryUpdate += 'UPDATE A SET A.Parte' + CONVERT(VARCHAR(10), @Contador) + ' = B.Parte FROM ##Tabela A JOIN #Partes B ON A.Conta = B.Conta WHERE B.Id = ' + CONVERT(VARCHAR(10), @Contador) + '; ' SET @Contador += 1 END SET @QueryCreate += ' )' ------------------------------------------------------ -- CRIA A TABELA DINAMICAMENTE ------------------------------------------------------ IF (OBJECT_ID('tempdb..##Tabela') IS NOT NULL) DROP TABLE ##Tabela EXEC(@QueryCreate) INSERT INTO ##Tabela ( Conta ) SELECT DISTINCT Conta FROM #Partes ------------------------------------------------------ -- ATUALIZA OS DADOS DINAMICAMENTE ------------------------------------------------------ EXEC(@QueryUpdate) ------------------------------------------------------ -- RESULTADO FINAL ------------------------------------------------------ SELECT * FROM ##Tabela |
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!
show essa muito utilizada em grupo e sub grupos itens uma cadeia aberta de níveis.