How Do `FOR XML PATH` and `STUFF` Combine to Concatenate Data in SQL Server?

The synergy of FOR XML PATH and STUFF functions in SQL Server

In SQL Server, the combination of FOR XML PATH and STUFF functions play a vital role in organizing and joining data. Let’s break down its mechanics to achieve the desired goal:

1. Use 'FOR XML PATH' to extract XML element string

FOR XML PATH Convert query results into XML elements. By omitting the element name, it outputs a comma-separated list of values, as shown in the example:

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

2. Use STUFF to remove leading commas

STUFF is used to modify a string by replacing specified characters. In this example, we remove the leading comma in the XML string:

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

3. Perform join to generate list

Finally, the modified list is joined with the original table via the 'id' column:

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

This combination effectively concatenates the 'Name' column values ​​for each 'Id' , resulting in the expected output:

<code>Id |    Name
-------------------
1   | aaa,bbb,ccc,ddd,eee</code>

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