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

The clever combination of STUFF and FOR XML PATH in SQL Server

In SQL Server, the combination of STUFF and FOR XML PATH functions can connect and process data efficiently. Let's see how these two functions work together to achieve the desired output.

The role of FOR XML PATH

FOR XML PATH Convert query results into a string of XML elements. Adding an empty string as argument ('') to FOR XML PATH produces a comma-separated list of values. For example:

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

The result is:

<code>,aaa,bbb,ccc,ddd,eee</code>

The role of STUFF

The STUFF function is used to insert or replace characters in a string. By specifying the starting position and length parameters as 1, you effectively remove the first character of the string:

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

This will remove the comma at the beginning of the name list:

<code>aaa,bbb,ccc,ddd,eee</code>

ID-based connection

Finally, use Group By to join the modified list with the Id column to aggregate and return the desired output:

<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 code succinctly explains how STUFF and FOR XML PATH work together in SQL Server to produce the desired output.

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