How to Eliminate Duplicates When Using STRING_AGG in SQL Server?

Eliminating Duplicates in STRING_AGG Results

The task is to retrieve unique values using the STRING_AGG function in SQL Server, ensuring that duplicate elements are removed.

In the provided example query, the STRING_AGG function is used to concatenate strings into a single comma-separated field, grouped by ProjectID. However, the resulting NewField contains duplicate values.

To obtain a unique NewField, we can utilize the DISTINCT keyword within a subquery. The updated query is:

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

The subquery first selects the distinct values of ProjectID and newId.value from the original table. These distinct values are then used in the outer query to perform the STRING_AGG operation, ensuring that no duplicates are present in the final NewField result.

Executing this modified query yields the desired output with only unique values:

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

The above is the detailed content of How to Eliminate Duplicates When Using STRING_AGG in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!