Get unique values using STRING_AGG in SQL Server

The following query returns the results shown below:

SELECT ProjectID, newID.value FROM [dbo].[Data] WITH(NOLOCK) CROSS APPLY STRING_SPLIT([bID],';') AS newID WHERE newID.value IN ('O95833', 'Q96NY7-2') 

Results:

ProjectID value --------------------- 2 Q96NY7-2 2 O95833 2 O95833 2 Q96NY7-2 2 O95833 2 Q96NY7-2 4 Q96NY7-2 4 Q96NY7-2 

Using the newly added STRING_AGG function (in SQL Server 2017) as it is shown in the following query I am able to get the result-set below.

SELECT ProjectID, STRING_AGG( newID.value, ',') WITHIN GROUP (ORDER BY newID.value) AS NewField FROM [dbo].[Data] WITH(NOLOCK) CROSS APPLY STRING_SPLIT([bID],';') AS newID WHERE newID.value IN ('O95833', 'Q96NY7-2') GROUP BY ProjectID ORDER BY ProjectID 

Results:

ProjectID NewField ------------------------------------------------------------- 2 O95833,O95833,O95833,Q96NY7-2,Q96NY7-2,Q96NY7-2 4 Q96NY7-2,Q96NY7-2 

I would like my final output to have only unique elements as below:

ProjectID NewField ------------------------------- 2 O95833, Q96NY7-2 4 Q96NY7-2 

Any suggestions about how to get this result? Please feel free to refine/redesign from scratch my query if needed.

8 Answers

Use the DISTINCT keyword in a subquery to remove duplicates before combining the results: SQL Fiddle

SELECT ProjectID ,STRING_AGG(value, ',') WITHIN GROUP (ORDER BY value) AS NewField from ( select distinct ProjectId, newId.value FROM [dbo].[Data] WITH(NOLOCK) CROSS APPLY STRING_SPLIT([bID],';') AS newID WHERE newID.value IN ( 'O95833' , 'Q96NY7-2' ) ) x GROUP BY ProjectID ORDER BY ProjectID 

You can use distinct in the subquery used for the apply:

SELECT d.ProjectID, STRING_AGG( newID.value, ',') WITHIN GROUP (ORDER BY newID.value) AS NewField FROM [dbo].[Data] d CROSS APPLY (select distinct value from STRING_SPLIT(d.[bID], ';') AS newID ) newID WHERE newID.value IN ( 'O95833' , 'Q96NY7-2' ) group by projectid; 

As @SeanLange pointed out in the comments, this is a terrible way to pull out the data, but if you had to, just make it 2 separate queries as follows:

SELECT ProjectID ,STRING_AGG( val, ',') WITHIN GROUP (ORDER BY val) AS NewField FROM ( SELECT DISTINCT ProjectID ,newID.value AS val FROM [dbo].[Data] WITH(NOLOCK) CROSS APPLY STRING_SPLIT([bID],';') AS newID WHERE newID.value IN ('O95833' , 'Q96NY7-2') ) t GROUP BY ProjectID 

That should do it.

This is a function that I wrote that answers the OP Title: Improvements welcome!

CREATE OR ALTER FUNCTION [dbo].[fn_DistinctWords] ( @String NVARCHAR(MAX) ) RETURNS NVARCHAR(MAX) WITH SCHEMABINDING AS BEGIN DECLARE @Result NVARCHAR(MAX); WITH MY_CTE AS ( SELECT Distinct(value) FROM STRING_SPLIT(@String, ' ') ) SELECT @Result = STRING_AGG(value, ' ') FROM MY_CTE RETURN @Result END GO 

Use like:

SELECT dbo.fn_DistinctWords('One Two Three Two One'); 

You can make a distinct view of the table, that holds the aggregate values, that is even simpler:

Create Table Test (field1 varchar(1), field2 varchar(1)); go Create View DistinctTest as (Select distinct field1, field2 from test group by field1,field2); go insert into Test Select 'A', '1'; insert into Test Select 'A', '2'; insert into Test Select 'A', '2'; insert into Test Select 'A', '2'; insert into Test Select 'D', '1'; insert into Test Select 'D', '1'; select string_agg(field1, ',') from Test where field2 = '1'; /* duplicates: A,D,D */; select string_agg(field1, ',') from DistinctTest where field2 = '1'; /* no duplicates: A,D */; 

Another possibility to get unique strings from STRING_AGG would be to perform these three steps after fetching the comma separated string:

1. Split the string (STRING_SPLIT)
2. Select DISTINCT from the splits
3. Apply STRING_AGG again to a select with a group on a single key

Example:

(select STRING_AGG(CAST(value as VARCHAR(MAX)), ',') from (SELECT distinct 1 single_key, value FROM STRING_SPLIT(STRING_AGG(CAST(customer_division as VARCHAR(MAX)), ','), ',')) q group by single_key) as customer_division 

Here is my improvement on @ttugates to make it more generic:

CREATE OR ALTER FUNCTION [dbo].[fn_DistinctList] ( @String NVARCHAR(MAX), @Delimiter char(1) ) RETURNS NVARCHAR(MAX) WITH SCHEMABINDING AS BEGIN DECLARE @Result NVARCHAR(MAX); WITH MY_CTE AS ( SELECT Distinct(value) FROM STRING_SPLIT(@String, @Delimiter) ) SELECT @Result = STRING_AGG(value, @Delimiter) FROM MY_CTE RETURN @Result END 

Oracle (since version 19c) suports listagg (DISTINCT ..., but Microsoft SQL Server not probably.