Get unique values using STRING_AGG in SQL Server
Solution 1:
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
Solution 2:
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;
Solution 3:
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.
Solution 4:
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');