How Do `STUFF` and `FOR XML PATH` Concatenate Names in SQL Server for Duplicate IDs?

SQL Server: Concatenating Names for Duplicate IDs using STUFF and FOR XML PATH

This guide demonstrates how to concatenate multiple names associated with duplicate IDs in a SQL Server table, resulting in a comma-separated list of names for each unique ID. We'll achieve this using the powerful combination of STUFF and FOR XML PATH.

Scenario:

Imagine a table with duplicate IDs and corresponding names. The goal is to create a new column containing all names for each ID, neatly separated by commas.

Solution:

The solution employs a three-step process:

Step 1: Generating the Comma-Separated XML String

The core of the solution lies in leveraging FOR XML PATH('') to generate a comma-separated string from the names.

<code class="language-sql">SELECT ',' + name
FROM temp1
FOR XML PATH('')</code>

This generates an XML string where each name is preceded by a comma.

Step 2: Removing the Leading Comma

The resulting XML string from Step 1 begins with an unnecessary comma. STUFF elegantly removes this.

<code class="language-sql">STUFF(
  (SELECT ',' + name FROM temp1 FOR XML PATH('')),
  1,
  1,
  ''
)</code>

STUFF replaces the first character (the leading comma) with an empty string.

Step 3: Joining, Grouping, and Final Result

Finally, we combine the above steps with a JOIN and GROUP BY clause to achieve the desired result.

<code class="language-sql">SELECT ID,
  ConcatenatedNames = STUFF(
    (SELECT ',' + name
    FROM temp1 t1
    WHERE t1.id = t2.id
    FOR XML PATH (''))
  , 1, 1, '')
FROM temp1 t2
GROUP BY id;</code>

This query joins the subquery (which generates the comma-separated string) with the original table (temp1) based on the ID. The GROUP BY clause ensures that the concatenation happens for each unique ID. The resulting ConcatenatedNames column contains the comma-separated list of names for each ID.

The above is the detailed content of How Do `STUFF` and `FOR XML PATH` Concatenate Names in SQL Server for Duplicate IDs?. For more information, please follow other related articles on the PHP Chinese website!