How to Eliminate Duplicates in SQL Server's STRING_AGG Function?

SQL Server's STRING_AGG function: Removing Duplicate Values

This article addresses the challenge of eliminating duplicate values within the STRING_AGG function in SQL Server 2017 and later versions. The standard STRING_AGG function doesn't directly support the DISTINCT keyword for unique value aggregation.

The Problem: Directly using DISTINCT with STRING_AGG to count and concatenate unique values isn't possible.

The Goal: Generate a concatenated string of unique values using STRING_AGG.

Illustrative Example:

Let's consider a Sitings table with State, City, and Siting columns:

<code>ID | State    | City      | Siting
---------------------------------
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</code>

A simple query using COUNT(DISTINCT Siting) and STRING_AGG(Siting, ',') yields:

State	City	# Of Types	Animals
Arizona	Flagstaff	1	dog
Florida	Orlando	2	dog,bird
Arizona	Phoenix	2	bird,bird,bird,dog,bird

The desired output, however, should remove duplicates from the "Animals" column for Phoenix:

State	City	# Of Types	Animals
Arizona	Flagstaff	1	dog
Florida	Orlando	2	dog,bird
Arizona	Phoenix	2	bird,dog

The Solution: A Double-Grouping Approach

To achieve this, we utilize a two-step grouping process with Common Table Expressions (CTEs):

<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')
    ) AS F (ID, State, City, Siting)
),
CTE_Animals AS (
    SELECT
        State, City, Siting
    FROM Sitings
    GROUP BY State, City, Siting
)
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>

The Result: This query successfully produces the desired output:

State	City	# Of Sitings	Animals
Arizona	Flagstaff	1	dog
Arizona	Phoenix	2	bird,dog
Florida	Orlando	2	dog,bird

This double-grouping technique first groups by State, City, and Siting to eliminate duplicates within each city, then groups again by State and City to concatenate the unique sitings.

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