How to Concatenate Fields from Multiple Records in T-SQL Using FOR XML PATH()?

Use FOR XML PATH() to join fields of multiple records in T-SQL

In your specific scenario, where you have multiple tables with a many-to-many relationship, you can use a subquery and the FOR XML PATH() function to join the required fields. Here's how to achieve the desired result:

<code class="language-sql">SELECT  *,
        ( 
            SELECT  u.FName + ', '
            FROM    @Users u INNER JOIN 
                    @Reviewers rs ON u.UserID = rs.UserID
            WHERE   rs.ReviewID = r.ReviewID
            FOR XML PATH('')
        ) AS Users
FROM    @Reviews r</code>

This query first joins the @Reviews, @Reviewers, and @Users tables to create a unified dataset. It then uses a subquery to select the FName column from @Users for each associated ReviewID. The FOR XML PATH() function concatenates these FName values ​​into a comma-separated string.

Finally, the generated connection string is assigned to the "Users" column in the main SELECT statement. The output looks like this:

<code>ReviewID    ReviewDate          Users
1           2009-01-12          Bob, Joe, Frank
2           2009-01-25          Sue, Alice</code>

The above is the detailed content of How to Concatenate Fields from Multiple Records in T-SQL Using FOR XML PATH()?. For more information, please follow other related articles on the PHP Chinese website!