none
Consulta avançada RRS feed

  • Pergunta

  • 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

    terça-feira, 15 de outubro de 2013 11:45

Respostas

  • 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

    terça-feira, 15 de outubro de 2013 19:56

Todas as Respostas

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

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

    terça-feira, 15 de outubro de 2013 13:01
  • 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

    terça-feira, 15 de outubro de 2013 13:45
  • 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.



    terça-feira, 15 de outubro de 2013 14:03
  • 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

    terça-feira, 15 de outubro de 2013 14:25
  • 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

    terça-feira, 15 de outubro de 2013 17:34
  • Quando faço um select simples

    Rodrigo Santos

    terça-feira, 15 de outubro de 2013 17:35
  • note aqui como ficou a query com a sua ajuda:

    Rodrigo Santos

    terça-feira, 15 de outubro de 2013 17:36
  • entao como deu erro no group by eu adicionei o campo que ficou faltando e ai gerou resultados

    Rodrigo Santos

    terça-feira, 15 de outubro de 2013 17:37
  • 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

    terça-feira, 15 de outubro de 2013 17:39
  • 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

    terça-feira, 15 de outubro de 2013 18:22
  • 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

    terça-feira, 15 de outubro de 2013 19:05
  • veja sem o group by o resultado:


    Rodrigo Santos

    terça-feira, 15 de outubro de 2013 19:08
  • é 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

    terça-feira, 15 de outubro de 2013 19:19
  • 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

    terça-feira, 15 de outubro de 2013 19:24
  • 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

    terça-feira, 15 de outubro de 2013 19:35
  • 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

    terça-feira, 15 de outubro de 2013 19:56
  • 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

    terça-feira, 15 de outubro de 2013 20:35