Outra alternativa para concatenar registros no SQL Server

Bom Dia Pessoal,

Esses dias tenho estudado bastante o SQL Server 2008 e as New Features já que comecei a maratona de certificações nesse produto. Hoje deparei-me com o tão repetido slogan "Beyond Relational" que aborda os novos tipos de dados. Para começar foi abordado o tipo de dados XML para trabalhar com esse padrão mais nativamente. Eu gosto muito do tema XML e das capacidades implementadas no SQL Server. Tive boas experiências com o 2005 e o 2008 só veio a somar com algumas novas funcionalidades. Aproveitei para verificar como utilizá-lo para resolver mais um problema comum que é a concatenação de registros.

Em alguns artigos passados eu já havia falado sobre diversas possibilidades de concatenar registros com CLR, cursores, subqueries, etc em "SQL Server 2005 T-SQL Tips: Concatenando e Agrupando" e "Concatenando registros". No assunto XML, demonstrei duas formas de fazer isso. Pensei eu que com tantas maneiras de fazer isso não haveria muito mais o que abordar, mas ao revisar o uso do XML no SQL Server 2008, acabei achando uma mais uma maneira de fazer essa concatenação. Vejamos um exemplo prático:

— Cria uma tabela de Empresas
CREATE TABLE Empresas (EmpresaID INT, EmpresaNome VARCHAR(70))

— Cria uma tabela de Ativos
CREATE TABLE Ativos (AtivoID INT, EmpresaID INT, AtivoNome CHAR(5))

— Insere alguns Setores
INSERT INTO Empresas VALUES (1,‘Petrobrás’)
INSERT INTO Empresas VALUES (2,‘Banco do Brasil’)
INSERT INTO Empresas VALUES (3,‘Itaú Holding’)
INSERT INTO Empresas VALUES (4,‘Banco Itaú’)

— Insere alguns Registros
INSERT INTO Ativos VALUES (1,1,‘PETR3’)
INSERT INTO Ativos VALUES (2,1,‘PETR4’)
INSERT INTO Ativos VALUES (3,2,‘BBAS3’)
INSERT INTO Ativos VALUES (4,3,‘ITSA2’)
INSERT INTO Ativos VALUES (5,3,‘ITSA3’)
INSERT INTO Ativos VALUES (6,3,‘ITSA4’)
INSERT INTO Ativos VALUES (7,4,‘ITUB3’)
INSERT INTO Ativos VALUES (8,4,‘ITUB4’)

— Faz um JOIN das duas tabelas
SELECT EmpresaNome As Empresa, AtivoNome As Ativo
FROM Empresas As E
INNER JOIN Ativos As A ON E.EmpresaID = A.EmpresaID

O uso do JOIN produz o seguinte resultado:

Empresa Ativo
Petrobrás PETR3
Petrobrás PETR4
Banco do Brasil BBAS3
Itaú Holding ITSA2
Itaú Holding ITSA3
Itaú Holding ITSA4
Banco Itaú ITUB3
Banco Itaú ITUB4

Mas e o resultado desejado fosse diferente ?

Empresa Ativos
Petrobrás PETR3 ; PETR4 ;
Banco do Brasil BBAS3 ;
Itaú Holding ITSA2 ; ITSA3 ; ITSA4 ;
Banco Itaú ITUB3 ; ITUB4 ;

Como eu havia falado de XML, uma das formas de fazer isso é utilizá-lo de forma aninhada e omitir a cláusula TYPE para que o XML não seja tipado. Ex:

SELECT EmpresaNome As Empresa,
    (SELECT AtivoNome As Ativo FROM Ativos As A
    WHERE E.EmpresaID = A.EmpresaID
    FOR XML RAW(‘A’)) As Ativos
FROM Empresas As E

O resultado é exibido abaixo:

Empresa Ativos
Petrobrás <A Ativo="PETR3"/><A Ativo="PETR4"/>
Banco do Brasil <A Ativo="BBAS3"/>
Itaú Holding <A Ativo="ITSA2"/><A Ativo="ITSA3"/><A Ativo="ITSA4"/>
Banco Itaú <A Ativo="ITUB3"/><A Ativo="ITUB4"/>

Como os dados em XML estão em um formato desde visto que a cláusula TYPE foi emitida, basta utilizar dois REPLACEs e efetuar a substituição. Ex:

;WITH Titulos As (
SELECT EmpresaNome As Empresa,
    (SELECT AtivoNome As Ativo FROM Ativos As A
    WHERE E.EmpresaID = A.EmpresaID
    FOR XML RAW(‘A’)) As Ativos
FROM Empresas As E)

SELECT Empresa,
    REPLACE(REPLACE(Ativos,‘<A Ativo="’,),‘"/>’,‘ ; ‘) As Ativos
FROM Titulos

Com o uso da CTE para melhorar a legibilidade do código e com os dois REPLACEs é possível chegar no resultado esperado:

Empresa Ativos
Petrobrás PETR3 ; PETR4 ;
Banco do Brasil BBAS3 ;
Itaú Holding ITSA2 ; ITSA3 ; ITSA4 ;
Banco Itaú ITUB3 ; ITUB4 ;

Eu já havia abordado essa construção em artigos anteriores. Há ainda uma terceira alternativa com o uso do XML para obter esse mesmo resultado. O SQL Server 2008 possui a função data() para trabalhar com XML. Essa função simplesmente converte nós XML para dados estruturados em um único nó. Se considerarmos cada ativo como um nó é possível simplesmente enumerá-los como texto através dessa função. Ex:

;WITH Titulos As (
SELECT EmpresaNome As Empresa,
    (SELECT AtivoNome As [data()] FROM Ativos As A
    WHERE E.EmpresaID = A.EmpresaID
    FOR XML PATH()) As Ativos
FROM Empresas As E)

SELECT Empresa, Ativos FROM Titulos

A utilização da função data(), enumerou todos os ativos de uma empresa em um único nó. Como a expressão XPath, exibiu o nó vazio (‘’), o resultado foi apenas a apresentação dos ativos separados por um espaço.

Empresa Ativos
Petrobrás PETR3 PETR4
Banco do Brasil BBAS3
Itaú Holding ITSA2 ITSA3 ITSA4
Banco Itaú ITUB3 ITUB4

Com a utilização de um REPLACE, basta substituir os espaços pelo delimitador desejado. Ex:

;WITH Titulos As (
SELECT EmpresaNome As Empresa,
    (SELECT AtivoNome As [data()] FROM Ativos As A
    WHERE E.EmpresaID = A.EmpresaID
    FOR XML PATH()) As Ativos
FROM Empresas As E)

SELECT Empresa, REPLACE(Ativos,‘ ‘,‘ ; ‘) + ‘ ; ‘ As Ativos FROM Titulos

O resultado obtido é exibido abaixo:

Empresa Ativos
Petrobrás PETR3 ; PETR4 ;
Banco do Brasil BBAS3 ;
Itaú Holding ITSA2 ; ITSA3 ; ITSA4 ;
Banco Itaú ITUB3 ; ITUB4 ;

O uso da função data() mostrou como utilizar o XML para concatenar registros, mas essa é uma alternativa um pouco restrita. Como ela pressupõe o uso de um espaço para fazer a separação, caso haja algum ativo com o espaço no nome, o uso do REPLACE não saberá distinguir o espaço da função data() do espaço no nome do ativo fazendo assim uma delimitação equivocada. Para essas situações, o uso das técnicas utilizadas nos artigos anteriores é mais eficaz.

[ ]s,

Gustavo

4 Respostas para “Outra alternativa para concatenar registros no SQL Server

  1. Apesar desses replaces me soarem a primeira vista como "gabiarra" ahuahua, a solução ficou muito interessante. Os casos em que precisei fazer concatenação eu normalmente utilizava uma variável auxiliar (nunca precisei nada muit avançado nesse sentido) e um select simples.Mas me diga uma coisa, chegou a testar essa solução com VÁRIOS registros? Conseguiu dar uma sacada na performance? Tenho um palpite de que se forem muito registros uma função CLR pode se comportar melhor, o que acha?Abraço!

  2. Oi Thiago,Pois é. Descobri essa solução muito por acaso e ela só veio a somar com as demais. Eu não testei ainda o desempenho de verdade em relação às demais, pois, normalmente ne deparo com soluções de baixa cardinalidade (cinco telefones, três últimos pedidos, seis carros, etc). Ainda não me deparei com uma concatenação com muitos itens (a coluna iria ficar muito grande e esteticamente deselegante), mas acredito que em um cenário desses o CLR pode levar vantagem. Agora é aguardar para aparecer um teste real.Abs,

  3. Kara, muito interessante! O que eu preciso é bem parecido com este exemplo, mas ainda não consegui desenvolver uma solução,segue o endereço: http://forum.imasters.uol.com.br/index.php?/topic/389729-select-concatenado-de-registros-distintos-e-relacionados-com-outra-tabela/Se alguém puder me ajuda, agradeço.Abs,Balta.

  4. Olá Elias,Assim como os colegas não entendi a explicação da sua dúvida no link postado.Você poderia detalhar melhor e postar no fórum do MSDN ? Se eu vir tentarei responder[ ]s,

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s