August 19, 2026 at 2:49 pm
Has anyone written a wrapper function (or similar) around STRING_AGG() to allow the retrieval of distinct values only?
If yes, I'd be interested to see it, thanks.
August 19, 2026 at 3:26 pm
in my past
have taken the #sql server# function and modified it to my needs
saved it as a -user defined function-
August 19, 2026 at 3:33 pm
Interesting q. No, haven't done that.
I guess easiest would be to use a subquery, which you're already familiar with, so that's not what you're looking for, but just to be complete:
SELECT STRING_AGG(..., ...) WITHIN GROUP(...)
FROM (
SELECT DISTINCT ...
FROM ...
) AS derived
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 19, 2026 at 3:51 pm
CREATE FUNCTION dbo.DISTINCT_STRING_AGG
(
@TableName NVARCHAR(128),
@ColumnName NVARCHAR(128),
@Separator NVARCHAR(10)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @SQL NVARCHAR(MAX);
DECLARE @Result NVARCHAR(MAX);
SET @SQL = '
SELECT @ResultOut = STRING_AGG(val, ''' + @Separator + ''')
FROM (
SELECT DISTINCT ' + @ColumnName + ' AS val
FROM ' + @TableName + '
) AS DistinctVals';
EXEC sp_executesql @SQL, N'@ResultOut NVARCHAR(MAX) OUTPUT', @ResultOut=@Result OUTPUT;
RETURN @Result;
END;
GO
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply