Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

Support DISTINCT for STRING_AGG

Currently STRING_AGG aggregates all strings passed as an input. It would be very useful to support DISTINCT, so it would concatenate unique strings only.

Example:
CREATE TABLE dbo.Test (a VARCHAR(255))
INSERT INTO dbo.Test (a)
VALUES('Str1')
,('Str2')
,('Str1')

SELECT STRING_AGG(a, ',') FROM dbo.Test t

It returns: 'Str1,Str2,Str1'

SELECT STRING_AGG(DISTINCT a, ',') FROM dbo.Test t

It would return: 'Str1,Str2'

116 votes
Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)

We’ll send you updates on this idea

Alexander Sharovarov shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

8 comments

Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)
Submitting...
  • Stavros Macrakis commented  ·   ·  Flag as inappropriate

    I agree.

    I came across this discussion as I was trying to figure out how to do this.

    Yes, I can build an additional level of query to solve this, but that's true for select distinct and
    count distinct, too.

    In fact, it might be useful to do this as a generic part of the OVER clause.

  • Alexander Sharovarov commented  ·   ·  Flag as inappropriate

    Vladimir Moldovanenko, the workaround works if you just want to aggregate one value. But it doesn't work that well when you need to aggregate a string as part of a larger query/report.

  • JL Sands commented  ·   ·  Flag as inappropriate

    It's great that SQL Server finally added it but please finish the task.

    Without DISTINCT support, it greatly reduces the benefit of the function. Most of the time STRING_AGG is used in conjunction with GROUP BY in the main query thus requiring a second sub-query just to provide distinct values to the STRING_AGG function is defeating its usefulness. Other ancient RDMS have supported DISTINCT this for decades.
    https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat

  • Vladimir Moldovanenko commented  ·   ·  Flag as inappropriate

    Workaround is fairly simple

    SELECT STRING_AGG(a, ',') FROM (SELECT DISTINCT a FROM dbo.Test) t

    However, why not support it? It makes T-SQL more powerful so this gets my vote

Feedback and Knowledge Base