Concatenate query [MSSQL]

Question

I have a question on how I could concatenate the email column, as query below:

select * from orcam INNER JOIN ccusto as a ON orcam.Ccusto = a.Ccusto INNER JOIN grupo_email b on a.Ccusto 
COLLATE SQL_Latin1_General_CP1_CI_AS = b.cc INNER JOIN grupo_resp c ON b.resp = c.id  where orcam.Ccusto=0151 and anomes=201709 

Who brings me the information:

Ccusto | Grupo | Anomes | Ccusto | cc | resp | id | email
0151     0452    201709   0151    0151  1      1    [email protected]
0151     0452    201709   0151    0151  1      2    [email protected]

I’m associating the column cc according to the column Ccusto. In this way, it brings me all those responsible for CCusto registered.

The problem, is that it is repeating to the same group for each email. What I am trying to do, is the output below

Ccusto | Grupo | Anomes | Ccusto |  cc  | email
0151     0452    201709   0151    0151   [email protected];[email protected]

Notice that he did not repeat the Grupo and concatenated the email02 after the first e-mail. The idea here is to concatenate all emails responsible for Ccusto 0151 for each group on the same line.

Personal thank you!

Answer 1

There are several ways, some easier depending on your version of Sql Server.

These answers can help you.

https://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string

Sql Example 2017

SELECT Ccusto , STRING_AGG(email, ', ') AS email
from orcam 
INNER JOIN ccusto as a ON orcam.Ccusto = a.Ccusto 
INNER JOIN grupo_email b on a.Ccusto COLLATE SQL_Latin1_General_CP1_CI_AS = b.cc 
INNER JOIN grupo_resp c ON b.resp = c.id  
where orcam.Ccusto=0151 and anomes=201709
GROUP BY Ccusto ;

Sql example 2005

    SELECT Ccusto ,
(
            Select email + ',' AS [text()]
            From grupo_resp 
            Where id = b.resp 
            For XML PATH ('')    
) AS email
    from orcam 
    INNER JOIN ccusto as a ON orcam.Ccusto = a.Ccusto 
    INNER JOIN grupo_email b on a.Ccusto COLLATE SQL_Latin1_General_CP1_CI_AS = b.cc 
    INNER JOIN grupo_resp c ON b.resp = c.id  
    where orcam.Ccusto=0151 and anomes=201709
    GROUP BY Ccusto ;

NOTE: As there was no example of table structure, maybe some field is wrong.