How to Concatenate Distinct Values Using STRING_AGG in SQL Server?

Using STRING_AGG for Distinct Value Concatenation in SQL Server: A Practical Approach

SQL Server's STRING_AGG function simplifies the concatenation of multiple values into a single string. However, directly achieving distinct value concatenation with STRING_AGG requires a workaround. This article outlines a two-step process to count and concatenate distinct values using STRING_AGG, even with large datasets.

The Solution:

This method uses a two-stage GROUP BY approach:

1. Eliminate Duplicates: The first GROUP BY operation removes duplicate rows, ensuring only unique combinations of your specified columns are considered.

   <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)
   )</code>

2. Aggregate and Concatenate: The second GROUP BY calculates the distinct count and concatenates the unique values using STRING_AGG.

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

Resulting Output:

The query yields a table summarizing the distinct counts and concatenated values:

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

This efficient technique ensures accurate distinct value concatenation and counting, even when dealing with extensive datasets. This approach effectively replicates the functionality of COUNT(DISTINCT <column>) while also providing the concatenated string.

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