Query - SQL Server with Stuff

Asked

Viewed 40 times

0

Guys, in the query below I want you to bring it in the following way.

contato
andreia, marcos, juliana

that is, on the same line. In the consultation, I used the stuff, but without success. Today she is bringing in this way.

contato

andreia
marcos
juliana

Follow the consultation:

--Contatos vinculados a contas cadastradas de 17/11/2015 até hoje, sem oportunidades cadastradas--

select distinct
U.UsuID Usuid, 
U.UsuNome Nome_Fantasia, U.UsuRazaoSocial Razão_Social, 
A.Descricao ABC,
U.UsuCGC CNPJ,
t.UsuNome,
U.UsuUF,
U.UsuCidade,
CP.CompDesc Qtde_CNPJs,
R.RamoDesc,
contato = STUFF(
             (SELECT ',' + t1.UsuNome
              FROM usuario t1
              WHERE t1.UsuID = ur.UsuID
               FOR XML PATH(''), TYPE).value('(./text())[1]', 'VARCHAR(MAX)') 1, 1, '')
from CRMDetalhesConta DCT
left join Usuario U
on (U.UsuID = DCT.ContaID)
left join CRMDetalhesContato DCO
on (DCT.ContaID = DCO.ContaID)
left join Usuario UR
on (UR.UsuID = DCO.ContatoID)
INNER JOIN ComplementoColunas C ON U.UsuID = C.UsuID AND C.LinhaID  = 1 
                                                     AND CompID     = 32 
                                                     AND C.ColunaID = 12
LEFT JOIN Complemento  CP     ON CP.UsuID = U.UsuID  AND CP.CompID  = 1
INNER JOIN Usuario      T     ON t.UsuID = c.ColunaValor
LEFT JOIN CRMRamo           R ON R.RamoID = DCT.RamoID
LEFT JOIN  CurvaABC     A     ON A.CurvaID = U.CurvaID
where t.UsuID in (38)
      AND U.UsuRazaoSocial IS NOT NULL  
      AND u.EmpLiberada= 1 AND U.UsuTipo = 'C' 
      AND U.UsuID NOT IN (select UsuIDCliente from Tarefa where TarTipID in (710) and TarStatus in (0,1,5))
      AND U.UsuID not in (select UsuIDCliente from Tarefa where TarTipID in (710) and CONVERT(DATE,TarFechamento) BETWEEN '01-01-2018' and getdate())
      and u.UsuID = 83295
order by U.UsuID
  • can put more detail of its result today ... for the columns U.Usuid Usuid, U.Usuname Name_fantasia, U.Usurazaosocial Razão_social, A.Descricao ABC, U.Usucgc CNPJ, t. Usuuf, U.Usuuf, U.Usucity, CP.Compdesc Qtde_cnpjs, R.Ramodesc, contact

  • https://stackoverflow.com/a/545672/2740371

  • @Marconciliosouza, other fields do not interfere.

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.