The STRING_AGG function in SQL Server is used to concatenate values from multiple rows into a single string, often separated by a specified delimiter. When you want to ensure that only unique values are considered in the concatenation, you can use the DISTINCT keyword within a subquery.
Here's the general syntax for using STRING_AGG with DISTINCT:
SELECT STRING_AGG(column_name, ', ') AS concatenated_values
FROM (
SELECT DISTINCT column_name
FROM your_table
) AS distinct_values;
Detailed Example
Suppose you have a table named Employees with a column named Department. You want to get a list of unique departments separated by commas.
Here’s how you would do it:
SELECT STRING_AGG(DISTINCT Department, ', ') AS UniqueDepartments
FROM Employees;
However, if DISTINCT doesn't work directly with STRING_AGG, you typically rely on subqueries or CTEs (Common Table Expressions). Here's how you would do this with a subquery:
SELECT STRING_AGG(Department, ', ') AS UniqueDepartments
FROM (
SELECT DISTINCT Department
FROM Employees
) AS DistinctDepartments;
Notes
1. Database Compatibility: The STRING_AGG function is available in SQL Server 2017 and later. Ensure your database version supports it.
2. Null Values: NULL values are ignored in the output of STRING_AGG.
3. Ordering: If you want to maintain a specific order in the concatenation, you can add the ORDER BY clause inside the STRING_AGG. Here's an example:
SELECT STRING_AGG(DISTINCT Department, ', ' ORDER BY Department) AS UniqueDepartments
FROM Employees;
This example ensures that the unique departments are listed in alphabetical order within the concatenated string.
By using this method, you can effectively concatenate unique values from a column in SQL Server, ensuring a tidy and distinct list.