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

Answer 2

A UDF would be an ok way to solve this.

Just define a T-SQL function (UDF) that takes an int param (product ID) and returns a string (concatenation of names associated with the product.) If your method's name is GetProductNames then your query might look like this:

SELECT p1.CategoryId, dbo.GetProductNames(p1.CategoryId)
FROM Northwind.dbo.Products p1
GROUP BY CategoryId

Answer 3

seems like you need the functionality of group_concat (from mysql). this has been addressed here for another test dataset: http://stackoverflow.com/questions/122942/how-to-return-multiple-values-in-one-column-t-sql

Answer 4

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%28SQL.90%29.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)

Answer 5

Try this:

 Declare @Revs Table 
 (RevId int Priimary Key Not Null,
  RevDt DateTime Null,
  users varChar(1000) default '')

 Insert @Revs (RevId, RevDt)
 Select Distinct ReviewId, ReviewDate
 From Reviews
 Declare @UId Integer
 Set @Uid = 0
 While Exists (Select * From Users
               Where UserID > @Uid)
 Begin
    Update @Revs Set
      users = users + u.fName + ', '
    From @Revs R 
       Join Reviewers uR On ur.ReviewId = R.RId
       Join users u On u.UserId = uR.UserId 
    Where uR.UserId = @UId
    Select @Uid = Min(UserId)
    From users
    Where UserId > @UId
  End
  Select * From @Revs

Answer 6

Select R.ReviewID, ReviewDate
, (Select  FName + ', ' 
   from Users 
   where UserID = R.UserID 
   order by FName FOR XML PATH(')
) as [Users]
from Reviews
inner join Reviewers AS R
  On Reviews.ReviewID = R.ReviewID
Group By R.ReviewID, ReviewDate;

Answer 7

Create a temp table to dump your data in. Then use the FOR XML PATH method. The outer query is needed to trim the last comma off the list.

CREATE TABLE #ReviewInfo (
ReviewId INT,
ReviewDate DATETIME,
Reviewer VARCHAR(1000))

INSERT INTO #ReviewInfo (ReviewId, ReviewDate, Reviewer)
SELECT r.ReviewId, r.ReviewDate, u.FName
FROM Reviews r
JOIN Reviewers rs ON r.ReviewId = rs.ReviewId
JOIN Users u ON u.UserId = rs.UserId

SELECT ReviewId, ReviewDate, LEFT(Users, LEN(Users)-1)
FROM (
SELECT ReviewId, ReviewDate, 
(
    SELECT Reviewer + ', '
    FROM #ReviewInfo ri2
    WHERE ri2.ReviewId = ri1.ReviewId
    ORDER BY Reviewer
    FOR XML PATH('')
) AS Users
FROM #ReviewInfo ri1
GROUP BY ReviewId, ReviewDate
) a

DROP TABLE #ReviewInfo

Answer 8

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>','')

Answer 9

I followed these instructions to create a SQL CLR Assembly and installed it in SQL Server 2008. It was quick to build and works great. http://technet.microsoft.com/en-us/library/ms131056.aspx

Don't forget to activate CLR functionality by running:

sp_configure 'clr enabled', 1 go reconfigure go