How to Concatenate Multiple Reviewers' First Names into a Single Comma-Separated String in T-SQL?

Combination of multiple record fields in T-SQL

In T-SQL, it is often necessary to group fields of multiple records based on another field. Consider the following scenario:

You have three tables: Reviews, Reviewers, and Users. The Reviews table contains ReviewID and ReviewDate. The Reviewers table contains ReviewerID, ReviewID and UserID. Finally, the Users table contains UserID, FName, and LName.

Challenge:

You want to display a list of reviews, each containing its corresponding review date and a comma-separated list of names of all reviewers associated with that review. The desired output looks like this:

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

instead of:

<code>ReviewID---ReviewDate---User
----------------------------
1----------12/1/2009----Bob
1----------12/1/2009----Joe
1----------12/1/2009----Frank
2----------12/9/2009----Sue
2----------12/9/2009----Alice</code>

Solution:

One way to solve this problem is to use the FOR XML PATH('') method:

<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>

Here’s a breakdown of the query:

• Line 1: Select the necessary columns from the Reviews table: ReviewID and ReviewDate.

• Lines 4-9: Use a subquery to retrieve a comma-separated list of names for each comment.

  The
  • subquery joins the Users and Reviewers tables based on UserID.
  • It selects the FName column and concatenates a comma after each name.
  The
  • FOR XML PATH('') section aggregates the results into a single string.

• Line 10: The result of the subquery is aliased as Users.

This revised output clarifies the alias and improves readability. The core functionality remains the same, efficiently concatenating reviewer first names into a comma-separated string.

The above is the detailed content of How to Concatenate Multiple Reviewers' First Names into a Single Comma-Separated String in T-SQL?. For more information, please follow other related articles on the PHP Chinese website!