STRING_AGG workaround for SQL 2016

Question

question

MichaelABreen-0252 asked Apr 25, '22 NaomiNNN answered Apr 28, '22

STRING_AGG workaround for SQL 2016

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:

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

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:

![196639-422project-2.png][1]

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

[1]: /answers/storage/attachments/196639-422project-2.png

sql-server-general sql-server-transact-sql

422project-2.png (26.4 KiB)

· 3

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YitzhakKhabinsky-0887 · Apr 25 at 11:45 PM

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)

0 ·

MichaelABreen-0252 YitzhakKhabinsky-0887 · Apr 26 at 03:49 PM

Thank you for your input. It has been noted and logged.

0 ·

GuoxiongYuan-7218 · Apr 27 at 04:06 PM

Please post each table DDL and sample data based on your output from your screenshot (https://docs.microsoft.com/en-us/answers/storage/attachments/196639-422project-2.png). So we can do the test.

0 ·

Answer 1 · 2022-04-28T02:13:52.8Z

LiHongMSFT-3908 answered Apr 28, '22 NaomiNNN commented Apr 28, '22

Hi @MichaelABreen-0252
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

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MichaelABreen-0252 · Apr 28 at 04:16 PM

This works exactly as I needed. Thank you.

0 ·

Answer 2 · 2022-04-25T21:05:28.897Z

NaomiNNN answered Apr 25, '22 MichaelABreen-0252 commented Apr 25, '22

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

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MichaelABreen-0252 · Apr 25 at 09:30 PM

Hi NaomiNNN:

If I remove the WHERE statement in line 9, I get every attribute associated to the site. It's a table that a lot of secondary and tertiary data get dumped into concerning each site, so I only want the information related to attribute 618.

How exactly would I write the exists subquery you reference in line 15?

Please forgive me, I'm still quite new to SQL in general.

0 ·

Answer 3 · 2022-04-25T21:33:28.363Z

NaomiNNN answered Apr 25, '22 NaomiNNN converted comment to answer Apr 28, '22

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

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MichaelABreen-0252 · Apr 26 at 03:56 PM

Hi NaomiNNN

I'm still getting people with multiple site assignments with separate lines for each assignment. I need one line per employee with all site assignments concatenated into the BuildingID field. I apologize if my question isn't clear. I've gone back and tried to make it more clear.

0 ·

Answer 4 · 2022-04-26T02:33:44.56Z

LiHongMSFT-3908 answered Apr 26, '22 MichaelABreen-0252 commented Apr 27, '22

Hi @MichaelABreen-0252
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.

· 4

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MichaelABreen-0252 · Apr 26 at 03:51 PM

Hi LiHong:

Thank you for your response. I'm still getting each person who is assigned to more than one site on separate lines. I need there to be one line for each person and have all assigned sites concatenated into a single string in the BuildingID column.

0 ·

JingyangLi MichaelABreen-0252 · Apr 26 at 05:20 PM

Another option is to use JSON query in SQL to get what you want. But you need to provide your sample database table markup. I can show you how to write that query.

0 ·

LiHongMSFT-3908 MichaelABreen-0252 · Apr 27 at 06:54 AM

Hi @MichaelABreen-0252
Since I don't know what the wrong output result is, could you please execute my query above and upload the screenshot of the wrong result.Then I could modify the query based on the wrong output.

Best regards,
LiHong

0 ·

MichaelABreen-0252 LiHongMSFT-3908 · Apr 27 at 03:57 PM

Hi LiHong:

I went back and posted a screenshot that shows both what I want and what I'm getting. I may have posted it after your first response, but it is there. It's an attachment (that's how the system made me post it) called 422project-2.png. Hopefully that will help.

Thanks,
Mike

0 ·

Answer 5 · 2022-04-26T16:27:40.707Z

NaomiNNN answered Apr 26, '22

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

· 2

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MichaelABreen-0252 · Apr 26 at 04:35 PM

I did and for some reason it ignored my "SORT BY" and put everything in some random order but the multiple building IDs remain. Then when I returned to the original code again, now I'm getting an error "Msg 8120, Level 16, State 1, Line 9
Column 'staffMember.schoolID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

0 ·

NaomiNNN MichaelABreen-0252 · Apr 26 at 05:23 PM

Let's do step by step. 1. Do the single query to select everything with sitename - use it as your test.
The second step would be to put information you want to group by into the outer query and use with GROUP BY and the other information into sub-select joined with the outer query on the group by columns.
If you end up with each staff member displaying all sites in your first query, something may be already wrong...

0 ·

Answer 6 · 2022-04-27T16:19:56.583Z

MichaelABreen-0252 answered Apr 27, '22 YitzhakKhabinsky-0887 commented Apr 27, '22

I give up!

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YitzhakKhabinsky-0887 · Apr 27 at 06:19 PM

@MichaelABreen-0252 ,

Please don't give up.
You never provided a minimal reproducible example.

What you need to do is very simple.
Edit your original question, and delete everything what you have there.
Copy ##1-4 from my earlier comment to the question, and use them as section headers
And provide exactly what it says ##1-4.

0 ·

Answer 7 · 2022-04-28T16:15:36.993Z

MichaelABreen-0252 answered Apr 28, '22

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.

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question