How Can I Get Distinct Results Using STRING_AGG in SQL Server 2017?

Generating Unique Values with SQL Server 2017's STRING_AGG Function

The STRING_AGG function in SQL Server 2017 doesn't directly support distinct values. However, achieving unique aggregated results is possible using a two-step approach. This method avoids the invalid syntax of STRING_AGG(DISTINCT column, ',').

The solution involves a Common Table Expression (CTE) to pre-process the data. The CTE groups the data to eliminate duplicates before the final aggregation.

The example below demonstrates this technique. The initial Sitings CTE provides sample data. The CTE_Animals CTE then groups by State, City, and Siting to remove redundant entries. The main query then uses STRING_AGG to concatenate the unique Siting values for each State and City combination.

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
)
SELECT
    State, City, COUNT(*) AS [# Of Sitings], STRING_AGG(Siting,',') AS Animals
FROM CTE_Animals
GROUP BY State, City
ORDER BY
    State, City;

This approach guarantees that the final Animals column contains only unique animal sightings for each location, while the # Of Sitings column reflects the distinct count. The ORDER BY clause ensures a consistent and predictable output order.

The above is the detailed content of How Can I Get Distinct Results Using STRING_AGG in SQL Server 2017?. For more information, please follow other related articles on the PHP Chinese website!