Concatenando registros

Boa Tarde,
 
Estive lendo alguns livros de SQL Server 2005 durante essa semana e me preparando para o Webcast de dicas e truques com o Transact-SQL na semana que vem. Para exercitar-me um pouco para esse Webcast, comecei a elencar algumas necessidades de consultas comuns e como resolvê-las de forma simples. Se eu fosse atender a todas, teria que reservar pelo menos umas três horas para o Webcast. Como sei que não possuirei três horas, postarei uma dica em T-SQL como aquecimento. Demonstrarei como concatenar vários registros por um caractér.
 
— Criação da tabela
CREATE TABLE tblContatos (Nome VARCHAR(50), DDD CHAR(3), Telefone CHAR(8))
 
— Insere registros
INSERT INTO tblContatos VALUES (‘Alberto’,‘061’,‘91237456’)
INSERT INTO tblContatos VALUES (‘Alberto’,‘061’,‘32262341’)
INSERT INTO tblContatos VALUES (‘Luiz’,‘062’,‘78134562’)
INSERT INTO tblContatos VALUES (‘Luiz’,‘062’,‘81235490’)
INSERT INTO tblContatos VALUES (‘Luiz’,‘062’,‘33610789’)
INSERT INTO tblContatos VALUES (‘Fernando’,‘041’,‘33456201’)
INSERT INTO tblContatos VALUES (‘Marcelo’,‘011’,‘35146723’)
INSERT INTO tblContatos VALUES (‘Marcelo’,‘011’,‘35146724’)
INSERT INTO tblContatos VALUES (‘Marcelo’,‘011’,‘35146725’)
INSERT INTO tblContatos VALUES (‘Marcelo’,‘011’,‘81224610’)
INSERT INTO tblContatos VALUES (‘Orlando’,‘031’,‘34120002’)
INSERT INTO tblContatos VALUES (‘Orlando’,‘031’,‘33701234’)
 
Para deixar a exibição um pouco melhor, criarei uma view para deixar os dados de uma forma mais apresentável. No caso do SQL Server 2005, as CTEs são bem vindas também.
 
— Cria uma View com os dados formatados
CREATE VIEW vwContatos (Nome, TelefoneCompleto)
AS
SELECT
Nome, ‘(‘ + DDD + ‘) ‘ + LEFT(Telefone,4) + ‘-‘ +
RIGHT (Telefone,4) FROM tblContatos
 
O truque consiste em tirar proveito de um BUG, melhor dizendo, de um determinado "comportamento não esperado" do SQL Server. A variável deve guardar apenas um único valor, mas ao atribuir o valor da variável a um conjunto de valores resultantes de uma query via SELECT a variável irá capturar o último valor, mas é capaz de acumular-se. Esse não deveria ser o comportamento esperado. Atribuir múltiplos valores de uma query a uma variável deve produzir um erro já que a variável é atômica. De fato isso ocorre com o SET mas não com o SELECT para nossa sorte.
 
— Mostra os telefones concatenados para Marcelo
— Cria e inicia uma variável
DECLARE @Telefones VARCHAR(1000)
SET @Telefones =
 
— Atribui o valor da variável
SELECT @Telefones = @Telefones + TelefoneCompleto + ‘; ‘ FROM vwContatos
WHERE Nome = ‘Marcelo’
 
— Mostra o valor da variável
PRINT @Telefones
 
Como pudemos observar, os telefones do Marcelo vieram todos concatenados por ;. Ex: (011) 3514-6723; (011) 3514-6724; (011) 3514-6725; (011) 8122-4610;. Quando o primeiro telefone foi lido, a variável @Telefones armazenou esse valor e acrescentou o ";", quando o segundo telefone foi lido, a variável capturou o segundo telefone, adicionou o ";" e combinou ao seu conteúdo anterior (o primeiro telefone). Esse comportamento foi repetido até que o último telefone fosse lido e armazenado na variável.
 
Esse comportamento não deveria ser o esperado. Como a variável só pode armazenar um valor, um erro deveria ser retornado, mas em função dessa anormalidade, pudemos montar a consulta e retornar o resultado desejado. O próximo passo é repetir isso para todos os demais contatos. Resista a tentação de criar um cursor para varrer os contatos, computar os telefones concatenados e gravar em uma tabela temporária. Podemos ter o mesmo resultado de forma mais eficiente utilizando uma função. Ex:
 
— Cria uma função para receber o nome e retornar os telefones
CREATE FUNCTION dbo.FNConcatTel (@Nome VARCHAR(50), @Caract CHAR(1))
RETURNS VARCHAR(1000)
AS
BEGIN
 
— Declara variável para acumular os telefones
DECLARE @Telefones VARCHAR(1000)
SET @Telefones = ”
 
SELECT @Telefones = @Telefones + TelefoneCompleto + @Caract
FROM vwContatos WHERE Nome = @Nome
 
RETURN(@Telefones)
 
END
 
Agora que a function está criada, basta obter uma lista de nomes (sem repetição) e aplicar a função para cada nome. Para melhor o desempenho, montei a lista primeiro e apliquei a função posteriormente. A aplicação do DISTINCT diretamente contra a tabela utilizando a função pode degradar o desempenho.
 
SELECT Nome, dbo.FNConcatTel(Nome,‘; ‘) AS Telefones
FROM
(SELECT DISTINCT Nome FROM tblContatos) AS Contatos
 
Essa solução é fácil de se implementar, mas ela tem duas desvantagens. A primeira desvantagem é que o retorno está restrito a 8.000 caractéres que é o tamanho máximo de um VARCHAR. Dificilmente uma lista de valores encadeados terá mais de 8.000 caractéres, mas é bom estar ciente desse problema (no SQL Server 2005 temos a disposição o VARCHAR(MAX) que pode superar essa limitação). A outra desvantagem é que essa estrutura de concatenação está fortemente atrelada a outros objetos (tivemos que criar uma View e uma Function para as concatenações). Se desejarmos repetir a mesma operação para outra tabela, teremos de criar mais uma view e mais uma function.
 
Em situações como essas é possível utilizar o CLR ou o XML. Já demonstrei como utilizá-las em um Webcast, mas para uma explicação detalhada não deixe de ver o artigo abaixo:
 
 
Lembre-se também de que em princípio não devemos utilizar o banco de dados para trabalhar formatação. A formatação deve ser feita em outras camadas e não no banco de dados. Se sua aplicação dispõe de recursos para formatar não utilize o banco de dados para essa finalidade. Bom, essa foi a dica de hoje, aproveitem.
 
[ ]s,
 
Gustavo

3 Respostas para “Concatenando registros

  1. boa noite gustavoAdorei a materia, tenho 1 problema mais ou menos igual a este que você apresentouporem envolve 2 tabelas1 contem 1 codigo de idexemplo: id = \’001021\’e na outra trabela tenho o id \’001021\’ varias vezes diferenciando o campo itemexemploid=\’001021\’ item=\’001\’ desc=\’qualquer coisa 1\’id=\’001021\’ item=\’002\’ desc=\’qualquer coisa 2\’id=\’001021\’ item=\’003\’ desc=\’qualquer coisa 3\’gostaria de realizar 1 query nesta primeira tabela retornando a descrição dos 3 campos concatenados no lugar do codigo, é possivel realizar está operação ?Desde já agradeço o post.Christian

  2. VOCÊ É O CARA!!!!!!! Era justamente isso que eu precisava para trabalhar com meus dados para o meu projeto final!!!

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