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!
