Concatenate row values T-SQL

Question

I am trying to pull together some data for a report and need to concatenate the row values of one of the tables. Here is the basic table structure:

Reviews

 ReviewID    ReviewDate   

Reviewers

 ReviewerID    ReviewID    UserID   

Users

UserID   FName   LName   

This is a M:M relationship. Each Review can have many Reviewers; each User can be associated with many Reviews.

Basically, all I want to see is Reviews.ReviewID, Reviews.ReviewDate, and a concatenated string of the FName's of all the associated Users for that Review (comma delimited).

Instead of:

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   

Display this:

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

I have found this article describing some ways to do this, but most of these seem to only deal with one table, not multiple; unfortunately, my SQL-fu is not strong enough to adapt these to my circumstances. I am particularly interested in the example on that site which utilizes FOR XML PATH() as that looks the cleanest and most straight forward.

SELECT p1.CategoryId, ( SELECT ProductName + ', '   FROM Northwind.dbo.Products p2   WHERE p2.CategoryId = p1.CategoryId   ORDER BY ProductName FOR XML PATH('') ) AS Products FROM Northwind.dbo.Products p1 GROUP BY CategoryId; 

Can anyone give me a hand with this? Any help would be greatly appreciated!

Answer 1

Have a look at this

DECLARE @Reviews TABLE(         ReviewID INT,         ReviewDate DATETIME )  DECLARE @Reviewers TABLE(         ReviewerID   INT,         ReviewID   INT,         UserID INT )  DECLARE @Users TABLE(         UserID  INT,         FName  VARCHAR(50),         LName VARCHAR(50) )  INSERT INTO @Reviews SELECT 1, '12 Jan 2009' INSERT INTO @Reviews SELECT 2, '25 Jan 2009'  INSERT INTO @Users SELECT 1, 'Bob', '' INSERT INTO @Users SELECT 2, 'Joe', '' INSERT INTO @Users SELECT 3, 'Frank', '' INSERT INTO @Users SELECT 4, 'Sue', '' INSERT INTO @Users SELECT 5, 'Alice', ''  INSERT INTO @Reviewers SELECT 1, 1, 1 INSERT INTO @Reviewers SELECT 2, 1, 2 INSERT INTO @Reviewers SELECT 3, 1, 3 INSERT INTO @Reviewers SELECT 4, 2, 4 INSERT INTO @Reviewers SELECT 5, 2, 5  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 Products FROM    @Reviews r