Concatenate query [MSSQL]

Question

Concatenate query [MSSQL]

Navigation

#1 by (0 votes)

0

I have a question on how I could concatenate the email column, as per the 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

What 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 am associating the cc column according to the Cost column. 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'm trying to do, is the output below

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

Note that it did not repeat Grupo and concatenated email02 after the first email. The idea here is to concatenate all emails responsible for Ccusto 0151 for each group on the same line.

Thank you guys!

sql-server

asked by anonymous 22.09.2017 / 23:48

1 answer

Glyphicon Bootstrap Manipulation Push notification icon - OneSignal

score 0 · Accepted Answer

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

These answers can help you.

link

Example Sql 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 ;

Example Sql 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: Since there was no example of the table structure, maybe some field is wrong.