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

Try:

 SELECT DISTINCT
     sta.lastName,
     sta.firstName,
     con.email,
     sta.staffNumber,
     '"' + STUFF((SELECT DISTINCT', ' + sna.[value]  /*The placement of the '"' puts double-quotes around the entire string in the column*/
         FROM siteName sna
      --   JOIN staffMember sta on sta.schoolID = sna.siteID
         WHERE sna.attributeID = 618 -- unnecessary here
         AND sna.SiteID = sta.ShoolID -- join with the outer query, not inner

FOR XML PATH('')),1,2,'') + '"' [Building ID(s)*]
 from staffMember sta
 JOIN contact con ON con.personID = sta.personID
 JOIN customSchool sna on sna.schoolID = sta.schoolID -- why do we need this join here - if we convert it into exists subquery would you get correct results?
 where sta.endDate IS NULL 
     AND sta.staffNumber is NOT NULL
     AND con.email is NOT NULL
     AND sna.attributeID = 618
 order by sta.lastName

Answer 3

Try:

SELECT DISTINCT
      sta.lastName,
      sta.firstName,
      con.email,
      sta.staffNumber,
      '"' + STUFF((SELECT DISTINCT', ' + sna.[value]  /*The placement of the '"' puts double-quotes around the entire string in the column*/
          FROM siteName sna

          WHERE sna.attributeID = 618 
          AND sna.SiteID = sta.ShoolID -- join with the outer query, not inner

 FOR XML PATH('')),1,2,'') + '"' [Building ID(s)*]
  from staffMember sta
  JOIN contact con ON con.personID = sta.personID

  where sta.endDate IS NULL 
      AND sta.staffNumber is NOT NULL
      AND con.email is NOT NULL
      AND EXISTS (select 1 from customSchool sna WHERE sna.schoolID = sta.schoolID and sna.attributeID = 618)
  order by sta.lastName

Answer 4

Hi @Michael "Captain Jack" Breen
Please check this query:

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 *',  
     '"' + C.value2 + '"'[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  
               CROSS APPLY (SELECT STUFF((SELECT DISTINCT', ' + A.[value]    
	                                      FROM customSchool A JOIN staffMember B on A.schoolID = B.schoolID AND A.schoolID=sta.schoolID  
                                          WHERE A.attributeID = 618 FOR XML PATH('')),1,2,'')AS value2)C  
 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  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

Instead of select distinct did you try:

SELECT 
       sta.lastName,
       sta.firstName,
       con.email,
       sta.staffNumber,
       '"' + STUFF((SELECT DISTINCT', ' + sna.[value]  /*The placement of the '"' puts double-quotes around the entire string in the column*/
           FROM siteName sna
          inner join staffMember sm ON sna.SiteID = sm.ShoolID -- join with the outer query, not inner
           WHERE sna.attributeID = 618 
           AND sm.StaffNumber = sta.StaffNumber -- should be PK of that table

  FOR XML PATH('')),1,2,'') + '"' [Building ID(s)*]
   from staffMember sta
   JOIN contact con ON con.personID = sta.personID

   where sta.endDate IS NULL 
       AND sta.staffNumber is NOT NULL
       AND con.email is NOT NULL
       AND EXISTS (select 1 from customSchool sna WHERE sna.schoolID = sta.schoolID and sna.attributeID = 618)
GROUP BY 
 sta.lastName,
       sta.firstName,
       con.email,
       sta.staffNumber,
   order by sta.lastName