- 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.
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, '.') |
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 |
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 |
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 |
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 |
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!
Essa soluĂ§Ă£o ajudou muito, eu estava procurando algo para usar no synapse e essa foi a melhor soluĂ§Ă£o
show essa muito utilizada em grupo e sub grupos itens uma cadeia aberta de nĂveis.