STRING_AGG giving different results than STUFF + FOR XML PATH

JasonW-5564 161 Reputation points
2021-09-15T05:45:05.22+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,139 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,586 questions
{count} votes

Accepted answer
  1. Tom Phillips 17,721 Reputation points
    2021-09-15T11:43:41.02+00:00

    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 person found this answer helpful.

3 additional answers

Sort by: Most helpful
  1. Olaf Helper 42,581 Reputation points
    2021-09-15T05:55:19.713+00:00

    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
    
    1 person found this answer helpful.
    0 comments No comments

  2. EchoLiu-MSFT 14,576 Reputation points
    2021-09-15T05:58:09.417+00:00

    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.

    1 person found this answer helpful.
    0 comments No comments

  3. Sean Williams 1 Reputation point
    2022-10-14T05:21:57.643+00:00

    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

    0 comments No comments