Consulta avançada

Question

Bom dia pessoal,

Tenho uma query no sql server que faço um join entre duas tabelas. Uma tabela de títulos no financeiro e outra tabela de tele cobranças onde são registrados as conversas de cobrança dos clientes. A tabela de títulos do financeiro tem o campo cliente e a tabela do tele cobranças também tem o código do cliente e foi feito então um inner join. So que para cada título no financeiro eu posso ter N registros do tele cobranças.

preciso juntar em uma única linha, todas as mensagens do tele cobrança, de forma concatenada como no exemplo abaixo onde TITULO, CLIENTE e VALOR são campos da tabela do financeiro e MENSAGEM é um campo do COBRANÇA:

TITULO         CLIENTE          VALOR        MENSAGEM

001              C2530              100,00      17/05/12 cliente com problema no celular pois não atende

001              c2530               100,00       18/08/12 cliente solicitou o parcelamento em 3 vezes

001              C2530               100,00       20/09/13   cliente disse que vai pagar com cheque

Preciso fazer uma query que me de para o exemplo acima o seguinte resultado:

TITULO      CLIENTE         VALOR     MENSAGEM

001           C2530           100,00    17/05/12 cliente com problema no celular pois não atende 18/08/12 cliente solicitou o parcelamento em 3 vezes 20/09/13 cliente disse que vai pagar com cheque

Ou seja 1 único registro com todas as mensagens concatenadas. Note que os demais campos são sempre idênticos, exceto a mensagem.

Alguém poderia me informar como farei isso no SQL server? Usando a linguagem de programação no caso ADVPL eu consigo, porém como vou desenvolver esse relatório no reporting services, preciso criar uma função para isso diretamente no sql server.

---

Rodrigo Santos

Answer 1

Bom dia,

Rodrigo, confira o tópico abaixo:

http://social.msdn.microsoft.com/Forums/sqlserver/pt-BR/1057670a-f0f3-4205-9f6d-985f74d84a32/consulta-agrupada

Espero que ajude.

---

Assinatura: http://www.imoveisemexposicao.com.br

Answer 2

Você pode criar um procedure, e utilizar um cursor nela para concatenar a descrição.

http://advplbrasil.com.br/advpl/manipulacao-base-de-dados/

Answer 3

OI Gapimex, acredito ser um pouco mais complexo, não expliquei direito....

A tabela do cobrança informada tem a seguinte estrutura

CHAVE   SEQ   TEXTO

001       01       17/05/12 cliente com problema

001       02       no celular pois não atende.

002       01       18/08/12 cliente solicitou o par

002       02       celamento em 3 vezes.

003       01       20/09/13   cliente disse que vai

003       02       pagar com cheque.

Onde para cada chave informada posso ter vários sequenciais. Esse sequencial serve para quebrar o texto de uma mensagem digitada pelo operador do tele cobrança.

se você for colocar todos os campos no resultado da query  ficaria mais ou menos assim:

TITULO         CLIENTE          VALOR     CHAVE   SEQ   MENSAGEM

001              C2530               100,00        001       01       17/05/12 cliente com problema

001              C2530               100,00        001       02       no celular pois não atende.

001              C2530               100,00        002       01       18/08/12 cliente solicitou o par

001              C2530               100,00        002       02       celamento em 3 vezes.

001              C2530               100,00        003       01       20/09/13   cliente disse que vai

001              C2530               100,00        003       02       pagar com cheque.

Note que no financeiro trata-se apenas de um único título no caso o 001 do cliente c2530 com valor de 100,00, porém como foi feito varias cobranças (3 no caso) gerou vários registros na tabela e cobrança (no caso 6 registros). Conforme dito preciso que fique assim (1 único registro):

TITULO      CLIENTE         VALOR     MENSAGEM

001              C2530           100,00    17/05/12 cliente com problema no celular pois não atende. 18/08/12 cliente solicitou o parcelamento em 3 vezes. 20/09/13 cliente disse que vai pagar com cheque.

Na verdade parece que terei que concatenar 2 vezes, uma na própria tabela do cobrança, que quebra as mensagens de um único atendimento em sequencial e outro concatenar para juntar todas as mensagens pois todas se referem ao mesmo título.

Deu para entender pessoal?

---

Rodrigo Santos

Answer 4

Rodigo,

utilize a dica do Gapimex

Crie uma procedure + ou menos assim:

CREATE PROCEDURE SP_JUNTATEXTO
@CHAVETABELA INT
AS

DECLARE @TEXTOCONTATENADO AS NVARCHAR(MAX), @DSCAMPO AS NVARCHAR(MAX)


DECLARE CUR_FINANCEIRO CURSOR FOR 
               
	SELECT DSCAMPO
		FROM 
			TITULO
		WHERE
			CHAVE = @CHAVE

   OPEN CUR_FINANCEIRO 

   FETCH NEXT FROM CUR_FINANCEIRO INTO
	@DSCAMPO



	SET 	@TEXTOCONTATENADO = ''
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @TEXTOCONTATENADO = @TEXTOCONTATENADO + @DSCAMPO

	FETCH NEXT FROM CUR_FINANCEIRO INTO
	@DSCAMPO

	END
   
	CLOSE CUR_FINANCEIRO
	DEALLOCATE CUR_FINANCEIRO
   SELECT TITULO,         CLIENTE ,         SUM(VALOR) VALOR, @TEXTOCONTATENADO FROM
         TITULO WHERE CHAVE = @CHAVE

gapimex

---

Marquinhos Não esqueça de qualificar a resposta.

Answer 5

Rodrigo, experimente fazer um teste com o script abaixo:

declare @TeleCobranca table
(TITULO int, CLIENTE varchar(20), VALOR money, CHAVE int, SEQ int, MENSAGEM varchar(50));

insert into @TeleCobranca values
(001, 'C2530', 100.00, 001, 01, '17/05/12 cliente com problema '),
(001, 'C2530', 100.00, 001, 02, 'no celular pois não atende.'),
(001, 'C2530', 100.00, 002, 01, '18/08/12 cliente solicitou o par'),
(001, 'C2530', 100.00, 002, 02, 'celamento em 3 vezes.'),
(001, 'C2530', 100.00, 003, 01, '20/09/13   cliente disse que vai '),
(001, 'C2530', 100.00, 003, 02, 'pagar com cheque.');

SELECT
    t.TITULO,
    t.CLIENTE,
    t.VALOR,
    STUFF
        ( (SELECT 
               case when s.SEQ = 1 then ', ' else '' end + s.MENSAGEM
           FROM @TeleCobranca as s
           WHERE s.TITULO = t.TITULO
           FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
           ,1, 2, '') as Mensagem
FROM @TeleCobranca as t
GROUP BY 
    t.TITULO,
    t.CLIENTE,
    t.VALOR

Espero que ajude.

---

Assinatura: http://www.imoveisemexposicao.com.br

Answer 6

Oi gapimex,

acredito que estamos quase lá..

rodando o seu exemplo funcionou 100%, porém aqui no bando de dados como expliquei os dados são provenientes de mais de uma tabela e não apenas de uma como você fez no exemplo e talvez por isso não esteja funcionando aqui comigo.

Veja bem na tabela do FINANCEIRO tenho os campos TITULO,CLIENTE,VALOR. Na tabela do TELECOBRANÇA que tenho os campos CHAVE, SEQ E MENSAGEM.

Como estou vendo que você está disposto a ajudar, vou postar algumas imagens que ajudem, se você quiser e puder gero script e te passo tudo que precisar para você rodar no seu banco.

---

Rodrigo Santos

Answer 7

---

Rodrigo Santos

Answer 8

note aqui como ficou a query com a sua ajuda:

---

Rodrigo Santos

Answer 9

entao como deu erro no group by eu adicionei o campo que ficou faltando e ai gerou resultados

---

Rodrigo Santos

Answer 10

Note que de 17 registros (antes com as mensagens truncadas) agora ficou 10 pois concatenou as mensagens porém todas essas mensagens são do mesmo titulo, cliente e valor e deveria resultar apenas uma como você demonstrou

---

Rodrigo Santos

Answer 11

Como no seu caso os dados estão em várias tabelas, acredito que não será necessário o Group By, então experimente remove-lo para ver se é obtido o resultado desejado.

---

Assinatura: http://www.imoveisemexposicao.com.br

Answer 12

Oi Gapimex, então sem o group by me mostra 17 registros como na imagem que segue.

Pensei se teria uma forma de criar uma tabela temporária em tempo de execução como você fez e depois utiliza-la da mesma forma que você executou, porém não sei fazer, teria como?

tipo assim:

Declare @TeleCobranca table
(TITULO int, CLIENTE varchar(20), VALOR money, CHAVE int, SEQ int, MENSAGEM varchar(50));

insert into @TeleCobranca values
--os valores seria atribuidos através de uma query quer faria no banco de dados
--e depois faria o select normalmente na @Telecobrança como você orientou

---

Rodrigo Santos

Answer 13

veja sem o group by o resultado:

---

Rodrigo Santos

Answer 14

é pessoal, eu ja fiz varias tentativas e sempre esta concatenando apenas o texto da tabela que contem as mensagens quebradas, ou seja, apenas para cada CHAVE e SEQ mas não para o TITULO, CLIENTE E VALOR.

encontrei também uma função que o Logan d. Merazzi postou em outra pergunta que também me deu o mesmo resultado... alguem sabe como resolver esse dilema?

 a função que localizei, ja sitado o Logan foi essa abaixo:

CREATE FUNCTION [dbo].[fnColunasParaLinhas](
  @NomeColuna varchar(10))
RETURNS varchar(Max)
AS
BEGIN   
DECLARE @str varchar(max)
--
SELECT @str = COALESCE(@str + ', ', '') + YP_TEXTO
FROM SYP030
WHERE  YP_CHAVE = @NomeColuna
--
RETURN @str
END

---

Rodrigo Santos

Answer 15

Rodrigo, acho que você pode fazer os Joins somente dentro da query que está dentro da função Stuff.

Você pode postar a query direto aqui no fórum como texto para que eu possa copiar o trecho e altera-lo conforme necessário?

---

Assinatura: http://www.imoveisemexposicao.com.br

Answer 16

ai gapimex, segue como ficou minha query:

SELECT  (E1.E1_PREFIXO+E1.E1_NUM+E1.E1_PARCELA+E1_TIPO) AS TITULO, E1.E1_CLIENTE,E1.E1_VALOR,
        STUFF
        (( SELECT CASE WHEN S.YP_SEQ = '001' THEN ',' ELSE '' END + S.YP_TEXTO
        FROM SYP030 S WHERE ACF.ACF_CODOBS = S.YP_CHAVE
        FOR XML PATH(''), TYPE).value('.','VARCHAR(MAX)')
        ,1,1,'') AS TEXTO
FROM    SE1030 E1
        INNER JOIN SA1030 A1 ON ( A1.D_E_L_E_T_ <> '*'   AND A1.A1_FILIAL = ' '   AND A1.A1_COD = E1.E1_CLIENTE AND A1.A1_LOJA = E1.E1_LOJA )
        LEFT OUTER JOIN ACG030 ACG ON ( ACG.D_E_L_E_T_ <> '*'  AND ACG.ACG_FILIAL = '  '  AND ACG.ACG_PREFIX = E1.E1_PREFIXO
                                        AND ACG.ACG_TITULO = E1.E1_NUM    AND ACG.ACG_PARCEL = E1.E1_PARCELA    AND ACG.ACG_TIPO = E1.E1_TIPO )
        LEFT OUTER JOIN ACF030 ACF ON ( ACF.D_E_L_E_T_ <> '*' AND ACF.ACF_FILIAL = '  ' AND ACF.ACF_CODIGO = ACG.ACG_CODIGO
                                        AND ACF.ACF_CLIENT = E1.E1_CLIENTE   AND ACF.ACF_LOJA = E1.E1_LOJA)
        LEFT OUTER JOIN SYP030 SYP ON (ACF.ACF_CODOBS = SYP.YP_CHAVE AND SYP.D_E_L_E_T_ = '')

WHERE   E1.D_E_L_E_T_ <> '*'
        AND E1.E1_PREFIXO BETWEEN '   ' AND 'ZZZ'
        AND E1.E1_NUM BETWEEN '      ' AND 'ZZZZZZ'
        AND E1.E1_CLIENTE BETWEEN '      ' AND 'ZZZZZZ'
        AND E1.E1_LOJA BETWEEN '  ' AND 'ZZ'
        AND E1.E1_TIPO IN ( 'BO', 'CA', 'DP', 'CH', 'CHP', 'FP' )
        AND E1.E1_VEND1 BETWEEN '      ' AND 'ZZZZZZ'
        AND E1.E1_EMISSAO BETWEEN '20120101' AND '20131231'
        AND E1.E1_VENCTO BETWEEN '20100801' AND '20131004'
        AND E1.E1_SALDO <> 0.00
        AND E1_PREFIXO+E1_NUM+E1_PARCELA = 'R12000016   1'
GROUP BY E1.E1_PREFIXO+E1.E1_NUM+E1.E1_PARCELA+E1.E1_TIPO, E1.E1_CLIENTE, E1.E1_VALOR , ACF.ACF_CODOBS

---

Rodrigo Santos

Answer 17

Experimente dessa forma:

SELECT  
    (E1.E1_PREFIXO + E1.E1_NUM + E1.E1_PARCELA + E1_TIPO) AS TITULO, 
    E1.E1_CLIENTE,
    E1.E1_VALOR,
    STUFF 
        ( (SELECT CASE WHEN S.YP_SEQ = '001' THEN ',' ELSE '' END + S.YP_TEXTO
           FROM ACG030 ACG 
           INNER JOIN ACF030 ACF 
               ON 
                   ACF.D_E_L_E_T_ <> '*' AND 
                   ACF.ACF_FILIAL = '  ' AND 
                   ACF.ACF_CODIGO = ACG.ACG_CODIGO AND 
                   ACF.ACF_CLIENT = E1.E1_CLIENTE AND 
                   ACF.ACF_LOJA = E1.E1_LOJA
           INNER JOIN SYP030 S
               ON 
                   ACF.ACF_CODOBS = S.YP_CHAVE AND 
                   S.D_E_L_E_T_ = ''
           WHERE
               ACG.D_E_L_E_T_ <> '*' AND 
               ACG.ACG_FILIAL = '  ' AND 
               ACG.ACG_PREFIX = E1.E1_PREFIXO AND 
               ACG.ACG_TITULO = E1.E1_NUM AND 
               ACG.ACG_PARCEL = E1.E1_PARCELA AND 
               ACG.ACG_TIPO = E1.E1_TIPO
           FOR XML PATH(''), TYPE).value('.','VARCHAR(MAX)')
           , 1, 1, '') AS TEXTO
FROM SE1030 E1
INNER JOIN SA1030 A1 
    ON
        A1.D_E_L_E_T_ <> '*' AND 
        A1.A1_FILIAL = ' ' AND 
        A1.A1_COD = E1.E1_CLIENTE AND 
        A1.A1_LOJA = E1.E1_LOJA 
WHERE -- todas as condições
-- sem Group By

Espero que ajude.

---

Assinatura: http://www.imoveisemexposicao.com.br

Answer 18

Gapimex, você é o cara!

te agradeço d+ mesmo pela paciência e tempo dedicado para a solução desse fórum.

eu já estava desistindo em função do tempo, e teria que desenvolver o mesmo usando linguagem de programação ADVPL, ia ficar muito tosco.

Agora vou poder fazer no reporting services. Vai ficar TOP.....

valeu mesmo....

---

Rodrigo Santos