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 … Read more
Here is one way to do it. Since you want the distinct counts as well, it can be done simply by grouping the rows twice. The first GROUP BY will remove duplicates, the second GROUP BY will produce the final result. WITH Sitings AS ( SELECT * FROM (VALUES (1, ‘Florida’, ‘Orlando’, ‘bird’), (2, ‘Florida’, … Read more
i know that’s not a real solution but you can change security mode: EXEC sp_configure ‘show advanced options’, 1 RECONFIGURE; EXEC sp_configure ‘clr strict security’, 0; RECONFIGURE; This is the easiest solution for those who want to continue their work