Visualizações: 61.236 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:
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' )
COALESCE
Como concatenar colunas em strings agrupadas utilizando COALESCE
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.
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:
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
UPDATE
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.
LOOPING COM CURSOR
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.
LOOPING COM WHILE
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.
CTE RECURSIVO
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.
FUNĂ‡ĂƒO SCALAR (UDF)
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.
SQL CLR (C#)
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.