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!

Solution 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

Solution 2

Turns out there is an even easier way to do this which doesn't require a UDF:

select replace(replace(replace((cast((
        select distinct columnName as X
        from tableName 
        for xml path('')) as varchar(max))), 
   '</X><X>', ', '),'<X>', ''),'</X>','')

Solution 3

Had similar problem and found a sweet solution after playing with code for 15 minutes

declare @result varchar(1000)
select @result = COALESCE(@result+','+A.col1, A.col1)
                FROM (  select  col1
                        from [table] 
                ) A
select @result

Returns result as value1,value2,value3,value4

Enjoy ;)

Solution 4

SqlServer 2017 now has STRING_AGG that aggregates multiple strings into one using a given separator.

Solution 5

There are 3 ways I have dealt with rolling-up data, as you have described, 1.use a cursor, 2.use a UDF or 3. use the a Custom Aggregate (written in .NET CLR).
The Cursor and UDF are pretty slow. (approx 0.1 sec per row). The CLR custom aggregate is surprisingly fast. (approx 0.001 sec per row)

Microsoft ships the code (to do exactly what you want) as part of the SDK for SQL 2005. If you have it installed, you should be able to find the code in this folder: C:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\StringUtilities. You might also want to this article in MSDN. It talks about installing the custom aggregate and enabling it: http://msdn.microsoft.com/en-us/library/ms161551(SQL.90).aspx

Once you compile and install the custom aggregate, you should be able to query like this:

SELECT Reviews.ReviewID, ReviewDate, dbo.StringUtilities.Concat(FName) AS [User]
FROM Reviews INNER JOIN Reviewers ON Reviews.ReviewID = Reviewers.ReviewID
   INNER JOIN Users ON Reviews.UserID = Users.UserID
GROUP BY ReviewID, ReviewDate;

and get a result set like you showed (above)

View more solutions