How Can I Use SQL Server's STRING_AGG to Get Distinct Values and Their Concatenated String?

Using SQL Server's STRING_AGG for Distinct Values and Concatenation

SQL Server's STRING_AGG function efficiently concatenates values from multiple rows. However, directly obtaining distinct values and their concatenated string requires a slightly more complex approach. The challenge lies in combining the functionality of COUNT(DISTINCT ...) with string aggregation.

A common solution involves a two-step grouping process. The first GROUP BY clause identifies unique combinations of relevant columns (e.g., State, City, Siting), eliminating duplicates. The second GROUP BY then aggregates these unique combinations, using STRING_AGG to concatenate the distinct values. This method provides both a count of distinct values and the concatenated string.

Here's an example demonstrating this technique:

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

This query produces a result set showing distinct animal sightings per city and state:

<code>+---------+-----------+--------------+----------+
|  State  |   City    | # Of Sitings | Animals  |
+---------+-----------+--------------+----------+
| Arizona | Flagstaff |            1 | dog      |
| Arizona | Phoenix   |            2 | bird,dog |
| Florida | Orlando   |            2 | bird,dog |
+---------+-----------+--------------+----------+</code>

Handling Long Strings:

If the concatenated string of sitings exceeds the 8000-character limit of varchar, explicitly casting the Siting column to varchar(max) before using STRING_AGG is necessary to avoid truncation:

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

This ensures that the concatenated string can accommodate longer results.

The above is the detailed content of How Can I Use SQL Server's STRING_AGG to Get Distinct Values and Their Concatenated String?. For more information, please follow other related articles on the PHP Chinese website!