How to Group and Concatenate User Names Associated with Multiple Reviews in T-SQL?

T-SQL: Combining User Names from Multiple Reviews

Aggregating data from multiple related tables into a single, comma-separated string is a frequent requirement in database management. This example demonstrates a T-SQL approach to achieve this, specifically for a many-to-many relationship between Reviews, Reviewers, and Users.

A concise solution using a subquery offers an effective method:

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

Here's a breakdown:

• @Reviews: This table stores review details (ReviewID, ReviewDate).
• @Reviewers: This table links reviews and reviewers (ReviewerID, ReviewID, UserID).
• @Users: This table contains user information (UserID, FName, LName).
• The inner SELECT statement joins @Users and @Reviewers to retrieve first names (FName) associated with each review.
• FOR XML PATH('') efficiently concatenates the FName values into a single string, separated by commas.

This query groups the results by ReviewID, producing a consolidated output where each review is represented by a single row, including its ReviewID, ReviewDate, and a comma-delimited list of associated user first names. This directly addresses the need to combine multiple user names related to each review into a single record.

The above is the detailed content of How to Group and Concatenate User Names Associated with Multiple Reviews in T-SQL?. For more information, please follow other related articles on the PHP Chinese website!