How to Get Unique Comma-Separated Values Using STRING_AGG in SQL Server?

Get Unique Values Using STRING_AGG in SQL Server

Problem:

Using the STRING_AGG function, you can retrieve comma-separated values from a column. However, the results may include duplicates. How do you get only the unique values in the output?

Query:

The following query uses STRING_AGG to get the unique values:

SELECT 
    ProjectID,
    STRING_AGG(DISTINCT 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

Explanation:

The DISTINCT keyword in the subquery eliminates duplicate values from the result set. The CROSS APPLY operator splits the bID column into individual values using the STRING_SPLIT function. The STRING_AGG function then concatenates these values into a comma-separated string, and the ORDER BY clause ensures that the values are sorted before concatenation.

Output:

The final output displays only the unique values for each ProjectID:

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

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