Olá pessoal,
Boa tarde!
Neste post vou falar sobre um recurso bem requisitado para a Microsoft, e que ela ainda não criou nativamente no SQL Server, que é concatenação de dados utilizando agrupamentos, já presente em outros SGBDs como MySQL (GROUP_CONCAT), Oracle (XMLAGG) e PostgreeSQL (STRING_AGG ou ARRAY_TO_STRING(ARRAY_AGG())).
Muita gente acha que esse recurso é a função CONCAT(), introduzida no SQL Server 2012, mas ela permite apenas a concatenação de várias colunas de uma linha em uma coluna e não a concatenação de várias linhas em uma coluna.
A concatenação de colunas em strings agrupadas, consiste em transformar linhas em uma string concatenada, de forma que você possa agrupar os valores por algum campo, e a outra coluna seja concatenada, conforme exemplificado abaixo:
Para criar a nossa tabela de testes, utilize os comandos abaixo:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
IF (OBJECT_ID('dbo.Teste_Group_Concat') IS NOT NULL) DROP TABLE dbo.Teste_Group_Concat CREATE TABLE dbo.Teste_Group_Concat( Categoria VARCHAR(100), Descricao VARCHAR(100) ) INSERT INTO dbo.Teste_Group_Concat( Categoria, Descricao ) VALUES ('Brinquedo', 'Bola'), ('Brinquedo', 'Carrinho'), ('Brinquedo', 'Boneco'), ('Brinquedo', 'Jogo'), ('Cama e Mesa', 'Toalha'), ('Cama e Mesa', 'Edredom'), ('Informatica', 'Teclado'), ('Informatica', 'Mouse'), ('Informatica', 'HD'), ('Informatica', 'CPU'), ('Informatica', 'Memoria'), ('Informatica', 'Placa-Mae'), (NULL, 'TV') |
Como concatenar colunas em strings agrupadas utilizando COALESCE
|
1 2 3 4 5 6 7 8 |
DECLARE @Nomes VARCHAR(MAX) SELECT @Nomes = COALESCE(@Nomes + ', ', '') + Descricao FROM dbo.Teste_XML SELECT @Nomes |
Como podemos observar acima, utilizando a função COALESCE, precisamos armazenar os resultados sempre em uma variável, e por este motivo, não conseguimos obter os resultados de forma agrupada por categoria, conforme precisamos. Se você quer apenas converter colunas em uma string, sem agrupar, essa solução irá te atender, mas não ao que precisamos para esse post.
Como concatenar colunas em strings agrupadas utilizando STUFF + FOR XML PATH
Uma forma prática e performática de se resolver esse problema, é utilizar o FOR XML PATH e o STUFF para realizarmos a concatenação agrupada, de forma que o resultado é exatamente aquilo que esperávamos:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT Categoria, STUFF(( SELECT ', ' + B.Descricao FROM dbo.Teste_Group_Concat B WHERE ISNULL(B.Categoria, '') = ISNULL(A.Categoria, '') ORDER BY B.Descricao FOR XML PATH('')), 1, 2, '' ) AS Descricao FROM dbo.Teste_Group_Concat A GROUP BY Categoria ORDER BY Categoria |
Como concatenar colunas em strings agrupadas utilizando UPDATE
|
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 |
DECLARE @Categoria VARCHAR(MAX), @Descricoes VARCHAR(MAX) DECLARE @Tabela TABLE ( Categoria VARCHAR(200), Descricao VARCHAR(200), Descricoes VARCHAR(200) ) INSERT @Tabela ( Categoria, Descricao ) SELECT Categoria, Descricao FROM dbo.Teste_Group_Concat ORDER BY Categoria, Descricao UPDATE @Tabela SET @Descricoes = Descricoes = COALESCE(CASE COALESCE(@Categoria, '') WHEN Categoria THEN @Descricoes + ', ' + Descricao ELSE Descricao END, ''), @Categoria = ISNULL(Categoria, '') SELECT Categoria, Descricoes = MAX(Descricoes) FROM @Tabela GROUP BY Categoria ORDER BY Categoria |
Mais uma solução que nos permitiu gerar os dados conforme nossa necessidade demanda.
Como concatenar colunas em strings agrupadas utilizando LOOPING com CURSOR
|
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 |
DECLARE @Tabela TABLE ( Categoria VARCHAR(MAX), Descricoes VARCHAR(MAX) ) INSERT @Tabela ( Categoria, Descricoes ) SELECT Categoria, '' FROM dbo.Teste_Group_Concat GROUP BY Categoria DECLARE @Categoria VARCHAR(MAX), @Descricao VARCHAR(MAX), @Descricoes VARCHAR(MAX) DECLARE c CURSOR LOCAL FAST_FORWARD FOR SELECT Categoria, Descricao FROM dbo.Teste_Group_Concat ORDER BY Categoria, Descricao OPEN c FETCH c INTO @Categoria, @Descricao WHILE @@FETCH_STATUS = 0 BEGIN UPDATE @Tabela SET Descricoes += ', ' + @Descricao WHERE ISNULL(Categoria, '') = ISNULL(@Categoria, '') FETCH c INTO @Categoria, @Descricao END CLOSE c DEALLOCATE c SELECT Categoria, Descricoes = STUFF(Descricoes, 1, 1, '') FROM @Tabela ORDER BY Categoria |
Essa solução também atendeu ao que precisamos, mas assim como todo looping, não é performático. Eu particularmente abomino a criação de cursores (a menos que você AINDA esteja no SQL Server 2000), em último caso, eu prefiro utilizar WHILE, mas temos soluções melhores aqui nesse post.
Como concatenar colunas em strings agrupadas utilizando LOOPING com WHILE
|
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 74 75 |
DECLARE @Tabela_Original TABLE ( Id INT IDENTITY(1, 1), Categoria VARCHAR(MAX), Descricao VARCHAR(MAX) ) INSERT @Tabela_Original ( Categoria, Descricao ) SELECT Categoria, Descricao FROM dbo.Teste_Group_Concat ORDER BY Categoria DECLARE @Tabela TABLE ( Id INT IDENTITY(1, 1), Categoria VARCHAR(MAX), Descricoes VARCHAR(MAX) ) INSERT @Tabela ( Categoria, Descricoes ) SELECT Categoria, '' FROM dbo.Teste_Group_Concat GROUP BY Categoria DECLARE @Categoria VARCHAR(MAX), @Descricao VARCHAR(MAX), @Descricoes VARCHAR(MAX), @Contador INT = 1, @Numero_Linhas INT = (SELECT COUNT(*) FROM @Tabela_Original) WHILE(@Contador <= @Numero_Linhas) BEGIN SELECT @Categoria = ISNULL(Categoria, ''), @Descricao = Descricao FROM @Tabela_Original WHERE Id = @Contador ORDER BY Categoria, Descricao UPDATE @Tabela SET Descricoes += ', ' + @Descricao WHERE ISNULL(Categoria, '') = ISNULL(@Categoria, '') SET @Contador = @Contador + 1 END SELECT Categoria, Descricoes = STUFF(Descricoes, 1, 1, '') FROM @Tabela ORDER BY Categoria |
Solução atendida, mas como todo looping, não é performática.
Como concatenar colunas em strings agrupadas utilizando CTE Recursivo
|
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 |
;WITH Dados as ( SELECT Categoria, CONVERT(NVARCHAR(MAX), Descricao) AS Descricao, ROW_NUMBER() OVER (PARTITION BY Categoria ORDER BY Descricao) AS Linha FROM dbo.Teste_Group_Concat ), Tabela1 AS ( SELECT Categoria, Descricao, Linha FROM Dados WHERE Linha = 1 ), Resultado AS ( SELECT Categoria, Descricao, Linha FROM Tabela1 WHERE Linha = 1 UNION ALL SELECT Dados.Categoria, Resultado.Descricao + N', ' + Dados.Descricao, Dados.Linha FROM Dados JOIN Resultado ON Resultado.Categoria = Dados.Categoria AND Dados.Linha = Resultado.Linha + 1 ) SELECT Categoria, Descricoes = MAX(Descricao) FROM Resultado GROUP BY Categoria ORDER BY Categoria OPTION (MAXRECURSION 0); |
Essa solução utiliza da técnica de recursividade para resolver nosso problema sem precisar de looping. Embora não seja muito fácil de entender a princípio, é uma solução prática e muito interessante.
Como concatenar colunas em strings agrupadas utilizando Função Scalar (UDF)
|
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 |
CREATE FUNCTION dbo.fncConcatCategoria ( @Ds_Categoria VARCHAR(MAX) ) RETURNS VARCHAR(MAX) WITH SCHEMABINDING AS BEGIN DECLARE @Retorno VARCHAR(MAX); SELECT @Retorno = COALESCE(@Retorno + ', ', '') + Descricao FROM dbo.Teste_Group_Concat WHERE ISNULL(Categoria, '') = ISNULL(@Ds_Categoria, '') ORDER BY Descricao RETURN @Retorno END GO SELECT Categoria, dbo.fncConcatCategoria(Categoria) AS Descricao FROM dbo.Teste_Group_Concat GROUP BY Categoria |
Função bem útil e após criada, se torna muito prática de utilizar. O problema dessa solução é que ela não é muito performática e exige que você crie uma função para cada tabela que você precise realizar essa solução, ou seja, acaba não sendo muito genérica e você precisaria criar várias funções que fazem praticamente a mesma coisa na sua base de dados.
Como concatenar colunas em strings agrupadas utilizando SCL CLR (C#)
|
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 |
using System; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.Text; [Serializable] [SqlUserDefinedAggregate( Format.UserDefined, IsInvariantToNulls = false, IsInvariantToDuplicates = false, IsInvariantToOrder = true, MaxByteSize = -1)] public struct concat : IBinarySerialize, INullable { private StringBuilder _accumulator; private string _delimiter; public bool IsNull { get; private set; } public void Init() { _accumulator = new StringBuilder(); _delimiter = string.Empty; this.IsNull = true; } public void Accumulate([SqlFacet(MaxSize = -1)]SqlString Value, [SqlFacet(MaxSize = -1)]SqlString Delimiter) { if (Value.IsNull) return; if (!Delimiter.IsNull & Delimiter.Value.Length > 0) { _delimiter = Delimiter.Value; if (_accumulator.Length > 0) _accumulator.Append(Delimiter.Value); } _accumulator.Append(Value.Value); IsNull = false; } public void Merge(concat Group) { if (_accumulator.Length > 0 & Group._accumulator.Length > 0) _accumulator.Append(_delimiter); _accumulator.Append(Group._accumulator.ToString()); } public SqlString Terminate() { return new SqlString(_accumulator.ToString()); } void IBinarySerialize.Read(System.IO.BinaryReader r) { _delimiter = r.ReadString(); _accumulator = new StringBuilder(r.ReadString()); if (_accumulator.Length != 0) this.IsNull = false; } void IBinarySerialize.Write(System.IO.BinaryWriter w) { w.Write(_delimiter); w.Write(_accumulator.ToString()); } } |
Minha solução favorita. Prática, extremamente rápida, genérica e resolve nosso problema. Não sabe o que é CLR ou sabe, mas não sabe como implementar na sua base de dados? Saiba mais acessando meu post Introdução ao SQL CLR (Common Language Runtime) no SQL Server.
Considerações de Performance
Vamos medir agora as nossas soluções e descobrir quais são as mais rápidas e as mais lentas.
CLR
STUFF + FOR XML PATH
UPDATE
CTE Recursivo
Função scalar UDF
Loop While
Loop com Cursor
De cara já podemos eliminar os loopings, que foram muito piores.. O que acontece se a gente inserir mais registros? Tipo umas 850.000 linhas.. Como será a performance?
CLR
STUFF + FOR XML PATH
Como nenhum outro método conseguiu terminar de processar em menos de 1 minuto, apesar de ter usado 850.000 linhas com o CLR e o FOR XML PATH, vou utilizar apenas 158.000 linhas (27%) para o restante dos métodos pra tentar analisar os resultados.. Vamos ver.
UPDATE
Até que o tempo com o UPDATE não foi tão ruim.. 5.7s para 158.000 linhas é um tempo razoável.
Função scalar UDF
CTE Recursivo
Bom, tentei esperar pelo CTE Recursivo.. Até fui almoçar e deixei rodando aqui, mas depois de 2 horas e 22 minutos não deu pra esperar mais terminar de processar as 158 mil linhas (lembrando que o CLR processou 850.000 em 3s e o FOR XML em 1.5s)..
Aparentemente, nosso vencedor foi o FOR XML PATH, mas se formos analisar os resultados, o CLR entregou o resultado correto, mesmo com toda essa quantidade de linhas absurda:
Já o FOR XML PATH… Acabou se perdendo e os resultados não ficaram na ordem correta.
Sendo assim, a solução que eu indico como a melhor de todas para esse tipo de situação, é o SQL CLR!
É isso aí, pessoal!
Obrigado pela visita e até o próximo post.
sql server clr function stuff for xml path recursive cte convert columns rows grouped concat concatenation string como converter concatenar colunas em string
sql server clr function stuff for xml path recursive cte convert columns rows grouped concat concatenation string como converter concatenar colunas em string
Concatenate many rows into a single text string
Concatenate many rows into a single text string
Parabéns Dirceu, por compartilhar essa informação tão valiosa, aproveito para acrescentar ao seu tópico um ‘plugin’ que encontrei após o seu blog, com quatro funções já prontas para o clr, bastando apenas adicionar ao banco.
http://groupconcat.codeplex.com/
André, obrigado pelo elogio e por compartilhar esse link. Vou dar uma olhada nele para aprender mais sobre.
Obrigado pela resposta, Dirceu!
Não me lembrava mais do SQL Sentry. Usei uma versão há muito tempo atrás, mas acho que nem era como essa. Já estou instalando aqui para avaliar de novo.
Valeu mesmo!
Dirceu,
Gostaria de dar os parabéns pelo artigo! Didático e bem prático, ficou tranquilo de entender e de comparar as diversas soluções.
Eu particularmente venho usando a solução do FOR XML com STUFF, que me atendeu bem até agora. Mas a solução SQL CLR ficou muito boa mesmo! Caso a se pensar para próximas implementações disso.
Só uma pergunta: você usou o SQL Server ou outro banco de dados? Na verdade, queria saber qual a ferramenta que você usou nessas comparações gráficas, que achei muito legal.
Obrigado!
Laércio
Laércio,
Boa tarde.
Muito obrigado pela visita e pelo feedback ?
Sobre a sua pergunta, o banco de dados é o SQL Server sim, mas a ferramenta que eu uso para analisar o plano de execução é o SQL Sentry Plan Explorer FREE (Vou fazer um post sobre essa ferramenta futuramente).
Na minha opinião, essa ferramenta oferece uma melhor visualização dos dados, além de alguns recursos para manipular as informações que o Management Studio do SQL Server não dispõe, mas daria para retornar algo parecido pelo próprio Management Studio sim.
Qualquer dúvida, é só falar.