How Can I Remove Duplicate Values from STRING_AGG Output in SQL Server?

Handling Duplicate Values within SQL Server's STRING_AGG Function

SQL Server's STRING_AGG function, introduced in SQL Server 2017, concatenates column values into a single string. However, it doesn't inherently remove duplicates. This article details a two-step approach to achieve distinct values within the aggregated string.

The Two-Step Solution

The key is to perform a distinct operation before using STRING_AGG. This involves a two-stage grouping process:

1. Initial Grouping for Distinct Values: First, group the data by the column(s) you want to be unique, along with any other necessary columns for your final result. This removes duplicate rows at the source.

   <code class="language-sql">WITH Sitings AS (
       SELECT * FROM (VALUES 
       (1, 'Florida', 'Orlando', 'bird'),
       (2, 'Florida', 'Orlando', 'dog'),
       (3, 'Arizona', 'Phoenix', 'bird'),
       (4, 'Arizona', 'Phoenix', 'dog'),
       (5, 'Arizona', 'Phoenix', 'bird'),
       (6, 'Arizona', 'Phoenix', 'bird'),
       (7, 'Arizona', 'Phoenix', 'bird'),
       (8, 'Arizona', 'Flagstaff', 'dog')
       ) F (ID, State, City, Siting)
   ), CTE_Animals AS (
       SELECT State, City, Siting
       FROM Sitings
       GROUP BY State, City, Siting
   )</code>

2. Final Grouping and Aggregation: Next, group the results from the first step by your desired columns and apply STRING_AGG to concatenate the distinct values.

   <code class="language-sql">SELECT
       State, City, COUNT(1) AS [# Of Sitings], STRING_AGG(Siting,',') AS Animals
   FROM CTE_Animals
   GROUP BY State, City
   ORDER BY State, City;</code>

Important Note on String Length:

If your concatenated string might exceed the 8000-character limit of VARCHAR, cast the values to VARCHAR(MAX) before using STRING_AGG:

<code class="language-sql">STRING_AGG(CAST(Siting AS VARCHAR(MAX)), ',') AS Animals</code>

This method effectively produces a STRING_AGG result containing only unique values.

The above is the detailed content of How Can I Remove Duplicate Values from STRING_AGG Output in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!