STRING_AGG workaround for SQL 2016

Question

I have a vendor who wants data organized into a single string in a column. I know this violates coding standards for databases, so please spare me the lectures on how I'm in the wrong. I have to provide the data the way the vendor wants it and that's just the way it has to be.

Everything I've found recommends using a STUFF function, but the results I'm getting are not what I want. This is what I want:

Smith | Robert | email@keyman .com | EmployeeID | "B1, B3, B6"
Jones | Melanie | email@tiedtlaw email .com | EmployeeID | "B2"

where the B1, B3, B6, and B2 are the site assignments for each individual's respective sites.

What I am getting is this:
Smith | Robert | email@keyman .com | EmployeeID | "B1, B2, B3, B3, B4, B5, B6"
Jones | Melanie | email@tiedtlaw email .com | EmployeeID | "B1, B2, B3, B3, B4, B5, B6"

Here is the code I have that is the closest I've been able to get:

SELECT DISTINCT  
 sta.lastName as 'Last Name *',  
 sta.firstName as 'First Name *',  
 con.email as 'Email Address *',  
 sta.staffNumber as 'Staff ID *',  
 'Y' as 'Is Active *',  
 '"' + STUFF((SELECT DISTINCT', ' + css.[value]  /*The placement of the '"' puts double-quotes around the entire string in the column*/  
 FROM customSchool css  
 JOIN staffMember sta on sta.schoolID = css.schoolID  
 WHERE css.attributeID = 618 FOR XML PATH('')),1,2,'') + '"' [Building ID{s}*],  
 'Y' as 'Certificate Holder *'  
from staffMember sta  
JOIN contact con ON con.personID = sta.personID  
JOIN customSchool css on css.schoolID = sta.schoolID  
where sta.endDate IS NULL   
    AND sta.staffNumber is NOT NULL  
 AND con.email is NOT NULL  
 AND css.attributeID = 618  
order by sta.lastName  

Any functional help would be appreciated.

>UPDATE<<

I may not have been completely clear on what I'm trying to do and I apologize for that. I've attached a mock up below of what I want and what I'm getting:

I appreciate the help thus far. I feel I'm so close. Again, all functional help will be appreciated.

Answer 1

Hi @Michael "Captain Jack" Breen
According to the screenshot you post, you need to modify the subquery of STUFF function:

 SELECT DISTINCT  
  sta.lastName as 'Last Name *',  
  sta.firstName as 'First Name *',  
  con.email as 'Email Address *',  
  sta.staffNumber as 'Staff ID *',  
  'Y' as 'Is Active *',  
  '"' + STUFF((SELECT DISTINCT', ' + C.[value]  /*The placement of the '"' puts double-quotes around the entire string in the column*/  
               FROM customSchool C JOIN staffMember S on S.schoolID = C.schoolID and S.StuffNumber=sta.StuffNumber  
               WHERE C.attributeID = 618 FOR XML PATH('')),1,2,'') + '"' [Building ID{s}*],  
  'Y' as 'Certificate Holder *'  
 from staffMember sta  
 JOIN contact con ON con.personID = sta.personID  
 JOIN customSchool css on css.schoolID = sta.schoolID  
 where sta.endDate IS NULL   
       AND sta.staffNumber is NOT NULL  
       AND con.email is NOT NULL  
       AND css.attributeID = 618  
 order by sta.lastName  

If it still doesn't work, please provide your table structure (CREATE TABLE …) and some sample data(INSERT INTO …). So that we’ll get a right direction and make some test.

Best regards,
LiHong

Answer 2

I give up!

Answer 3

Thank you LiHong. That worked EXACTLY as I wanted. When I get more time, I'll compare mine to yours and see where I went wrong. That's going to be one I keep around for a long time.

Thank you again.