How to Get Unique Values in a Comma-Separated String Using SQL Server's STRING_AGG?

Get Unique Values Using STRING_AGG and DISTINCT in SQL Server

The SQL Server STRING_AGG function, introduced in SQL Server 2017, allows you to aggregate strings from multiple rows into a single comma-separated string. However, when used with STRING_SPLIT to separate values in a comma-separated field, it can result in duplicate values in the aggregated string.

To obtain unique values in the aggregated string, you can enhance your query by using the DISTINCT keyword in a subquery. The modified 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

In this improved query:

• The subquery (SELECT DISTINCT ProjectID, newId.value FROM [dbo].[Data] WITH (NOLOCK) CROSS APPLY STRING_SPLIT([bID],';') AS newId WHERE newId.value IN ( 'O95833' , 'Q96NY7-2' )) uses the DISTINCT keyword to remove duplicate values from the result set.
• The STRING_AGG function is applied to the distinct values, ensuring that only unique elements are included in the aggregated string.

The output of this modified query will be:

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

This gives you the desired result, with unique values aggregated using STRING_AGG.

The above is the detailed content of How to Get Unique Values in a Comma-Separated String Using SQL Server's STRING_AGG?. For more information, please follow other related articles on the PHP Chinese website!