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.270 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