question

JasonW-5564 avatar image
0 Votes"
JasonW-5564 asked TZacks-2728 commented

STRING_AGG giving different results than STUFF + FOR XML PATH

I will admit I am no expert in using STUFF + FOR XML PATH! I am trying to improve a query that gets executed multiple times and 48% of that execution time is in the FOR XML PATH part of the query. We are running SQL Server 2017 so I am trying to replace it with the new STRING_AGG function instead. Below are my two attempts at each. I am getting different results for each. I am trying to get the same results so I can just replace the FOR XML PATH code and hopefully see some performance gains. Hopefully this is easy to follow as I have pulled this out of a much larger query just to isolate these calls.


--Query


--XML PATH
Select
STUFF(','+(
select distinct im.shortcode FROM issuer_metadata im FOR xml path('')
, type ).value('.', 'varchar(max)'),1,1,'')



--STRING_AGG FUNCTION

select distinct string_agg(im.shortcode,'')
FROM issuer_metadata im


--Results: Note different results


--XML Path : BACBCSBMOBNPBNSCCIBCCSGSHSBCIFCJPMMSNTXRBCSEKSGTC2TDUBSWFBWFC
--String_Agg: BCSBMOCGSHSBCJPMMSWFBBACBCSBMOBNPBNSCCIBCCSGSHSBCIFCJPMMSNTXRBCSEKTC2TDUBSWFCBCSCCSGSHSBCJPMMSRBCSGTDUBSBNP






sql-server-generalsql-server-transact-sql
· 4
5 |1600 characters needed characters left characters exceeded

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


STUFF and ','+ does not seem necessary.


0 Votes 0 ·

Just note, that in both of your solutions none of the aggregations is sorted.
When it's not deterministic, you can get different results even on the exact same code.

0 Votes 0 ·

@JasonW-5564,

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 Votes 0 ·

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

0 Votes 0 ·
TomPhillips-1744 avatar image
1 Vote"
TomPhillips-1744 answered TZacks-2728 commented

To get the same results, your command should be:
select string_agg( shortcode,'')
FROM (
select distinct im.shortcode FROM issuer_metadata
) a

There is no way to string_agg a distinct list currently.

· 1
5 |1600 characters needed characters left characters exceeded

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

Good example

0 Votes 0 ·
OlafHelper-2800 avatar image
1 Vote"
OlafHelper-2800 answered

Of course you get a different result.
In the XML solution you query first a distinct resultset of all "shortcode" and stuff then as result.
In String_Agg you query all "shortcode", also dulicates, agg them and retrieve the distinct result, which is the same as without the distinct, because it's one result.

You will get the same result using a subquery to get first the distinct list

 select string_agg(sub.shortcode,'')
 from 
     (select distinct im.shortcode
      FROM issuer_metadata im) as sub
5 |1600 characters needed characters left characters exceeded

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

EchoLiu-msft avatar image
1 Vote"
EchoLiu-msft answered EchoLiu-msft edited

Hi @JasonW-5564,

Please try:

     Select
     STUFF((select distinct ' '+CAST(im.shortcode AS VARCHAR(30)) AS [text()]
     FROM issuer_metadata im 
     FOR XML PATH('')), 1, 1, NULL) AS Abbr

If you have any question, please feel free to let me know.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.

5 |1600 characters needed characters left characters exceeded

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