How can I concatenate all the occurrences (1..N) in this query/relation (SQL Server)?

Question

I've got this query:

SELECT R.Unit, LU.ReportName, R.Generated, E.EmailAddr, R.BeginDate, R.EndDate
FROM ReportsGenHistory R
JOIN ReportsLU LU ON R.ReportID = LU.ReportID
JOIN ReportsUnitEmails E ON R.Unit = E.Unit AND R.ReportID = E.ReportID;

...for these tables:

CREATE TABLE ReportsGenHistory
(
    ID int IDENTITY(1,1) PRIMARY KEY,
    Unit VarChar(25)  NOT NULL,
    ReportID int NOT NULL,
    BeginDate DateTime NOT NULL,
    EndDate DateTime NOT NULL,
    Generated DateTime NOT NULL,
    GeneratedBy varchar(50),
);

// Lookup table
CREATE TABLE ReportsLU
(
    ReportID INT NOT NULL PRIMARY KEY,
    ReportID int NOT NULL
);

// 1..N table (a report can be emailed to N people)
CREATE TABLE ReportsUnitEmails
(
    Unit VarChar(25)  NOT NULL,
    ReportID int NOT NULL,
    EmailAddr VarChar(64)  NOT NULL,
    Created DateTime,
    CreatedBy varchar(50),
    CONSTRAINT pk_ReportsUnitEmailsCombinedKey PRIMARY KEY (Unit, ReportID, EmailAddr)
);

The problem is that, as written, the query will (I think) just return one matching EmailAddr from ReportsUnitEmails, even though there might be more. What I want to do is concatenate all matching EmailAddr values into one calculated field, so that the result set of the query might have records like:

R.Unit      LU.ReportName       R.Generated
------      -------------       -----------
Big Red     Price Compliance    2/28/2016
Big Blue    Produce Usage       2/29/2016
Green       Delivery Perf.      3/2/2016

E.EmailAddr                                         R.BeginDate R.EndDate
-----------                                         ----------- ---------
bclayshannon@gmail.com                              1/14/2016   1/21/2016
cshannon@fbi.gov;joesmith@att.net;nobody@home.com   1/1/2015    1/31/2016
axx3andspace@att.net;bo@pres.org                    1/1/2015    1/1/2016

How must I tweak my SQL so as to fold, spindle, and mutilate the Email Addresses into one comma-delimited field?

score 1 · Accepted Answer · answered Feb 19 '16 at 00:28

One way is to use FOR XML PATH to concatenate email addresses into one string:

SELECT 
      R.Unit, 
      LU.ReportName, 
      R.Generated, 
      STUFF((SELECT ','+EmailAddr FROM ReportsUnitEmails E WHERE R.Unit = E.Unit AND R.ReportID = E.ReportID FOR XML PATH('')),1,1,'') AS EmailAddr, 
      R.BeginDate, 
      R.EndDate
FROM ReportsGenHistory R
JOIN ReportsLU LU ON R.ReportID = LU.ReportID;

How can I concatenate all the occurrences (1..N) in this query/relation (SQL Server)?

1 Answers1