STRING_AGG giving different results than STUFF + FOR XML PATH

Question

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

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

string_agg does not work distinct.
Adding the distinct before the string_agg is pointless as there is only one result.

The only way I have been able to solve this for now is ( without doing a select as the join but essentially the same )

WITH cte
AS
(SELECT DISTINCT
im.shortcode
FROM issuer_metadata AS im)
SELECT STRING_AGG(cte.shortcode, '')
FROM cte;

this has been a gripe for a while as to why it's not possible to do SELECT STRING_AGG( DISTINCT shortcode ) from issuer_metadata