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

SQL Server – Como concatenar linhas agrupando os dados por uma coluna (Grouped concatenation)

Visualizações: 61.916 views
Tempo de Leitura: 8 minutos

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:

SQL Server - Grouped Concatenation convert rows into string

Para criar a nossa tabela de testes, utilize os comandos abaixo:

COALESCE

Como concatenar colunas em strings agrupadas utilizando COALESCE

SQL Server - Grouped Concatenation convert rows into string - COALESCE

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.

STUFF + FOR XML PATH

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:

SQL Server - Grouped Concatenation convert rows into string - STUFF FOR XML PATH

UPDATE

Como concatenar colunas em strings agrupadas utilizando UPDATE

SQL Server - Grouped Concatenation convert rows into string - UPDATE

Mais uma solução que nos permitiu gerar os dados conforme nossa necessidade demanda.

LOOPING COM CURSOR

Como concatenar colunas em strings agrupadas utilizando LOOPING com CURSOR

SQL Server - Grouped Concatenation convert rows into string - CURSOR

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.

LOOPING COM WHILE

Como concatenar colunas em strings agrupadas utilizando LOOPING com WHILE

SQL Server - Grouped Concatenation convert rows into string - WHILE

Solução atendida, mas como todo looping, não é performática.

CTE RECURSIVO

Como concatenar colunas em strings agrupadas utilizando CTE Recursivo

SQL Server - Grouped Concatenation convert rows into string - RECURSIVE CTE

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.

FUNÇÃO SCALAR (UDF)

Como concatenar colunas em strings agrupadas utilizando Função Scalar (UDF)

SQL Server - Grouped Concatenation convert rows into string - SCALAR FUNCTION UDF

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.

SQL CLR (C#)

Como concatenar colunas em strings agrupadas utilizando SCL CLR (C#)

SQL Server - Grouped Concatenation convert rows into string - SQL CLR CSHARP

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
SQL Server - Grouped Concatenation convert rows into string - Performance - CLR

STUFF + FOR XML PATH
SQL Server - Grouped Concatenation convert rows into string - Performance - STUFF FOR XML PATH

UPDATE
SQL Server - Grouped Concatenation convert rows into string - Performance - UPDATE

CTE Recursivo
SQL Server - Grouped Concatenation convert rows into string - Performance - CTE

Função scalar UDF
SQL Server - Grouped Concatenation convert rows into string - Performance - UDF Function

Loop While
SQL Server - Grouped Concatenation convert rows into string - Performance - WHILE

Loop com Cursor
SQL Server - Grouped Concatenation convert rows into string - Performance - 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
SQL Server - Grouped Concatenation convert rows into string - Performance - CLR2

STUFF + FOR XML PATH
SQL Server - Grouped Concatenation convert rows into string - Performance - 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
SQL Server - Grouped Concatenation convert rows into string - Performance - 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
SQL Server - Grouped Concatenation convert rows into string - Performance - UDF Function3

CTE Recursivo
SQL Server - Grouped Concatenation convert rows into string - Performance - CTE Recursive

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:
SQL Server - Grouped Concatenation convert rows into string - Performance - CLR4

Já o FOR XML PATH… Acabou se perdendo e os resultados não ficaram na ordem correta.
SQL Server - Grouped Concatenation convert rows into string - Performance - STUFF FOR XML PATH4

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