How can I group fields from multiple records in T-SQL by another field?

Question

I am trying to concatenate rows 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;

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

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 3

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

Answer 4

SQL Server 2017 has the STRING_AGG function that aggregates multiple strings into one using a given separator.

Answer 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)

Answer 6

select p1.Availability ,COUNT(*),
(select  name+','  from AdventureWorks2008.Production.Location p2 where 
p1.Availability=p2.Availability for XML path(''),type).value('.','varchar(max)') 
as Name  from AdventureWorks2008.Production.Location p1 group by Availability

Result

Availability  COUNT     Name  
---------------------------------------------------------------------------------
0.00    7   Tool Crib,Sheet Metal Racks,Paint Shop,Paint Storage,Metal 
                    Storage,Miscellaneous Storage,Finished Goods Storage,
80.00   1   Specialized Paint,
96.00   1   Frame Forming,
108.00  1   Frame Welding,
120.00  4   Debur and Polish,Paint,Subassembly,Final Assembly,

Answer 7

Now from SQL server 2017 there is a new T-SQL function called STRING_AGG:
it is a new aggregate function that concatenates the values of string expressions and places separator values between them.
The separator is not added at the end of string.

Example:

SELECT STRING_AGG ( ISNULL(FirstName,'N/A'), ',') AS csv 
FROM Person.Person; 

the result set:

John,N/A,Mike,Peter,N/A,N/A,Alice,Bob

Answer 8

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 9

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 10

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 11

seems like you need the functionality of group_concat (from mysql). this has been addressed here for another test dataset: How to return multiple values in one column (T-SQL)?

Answer 12

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 13

select 
      p1.Availability,
      COUNT(*),
      (
          select  name+',' 
          from AdventureWorks2008.Production.Location p2 
          where p1.Availability=p2.Availability 
          for XML path(''),type
      ).value('.','varchar(max)') as Name  
 from AdventureWorks2008.Production.Location p1 
 group by Availability

Answer 14

When the number of items is small this can be done using ROW_NUMBER() OVER PARTITION BY:

declare @t table (col1 int, col2 varchar)
insert into @t VALUES (1,'A')
insert into @t VALUES (1,'B')
insert into @t VALUES (1,'C')
insert into @t VALUES (1,'D')
insert into @t VALUES (1,'E')
insert into @t VALUES (2,'X')
insert into @t VALUES (3,'Y')

select col1,
    MAX(CASE seq WHEN 1 THEN        col2 ELSE '' END ) + 
    MAX(CASE seq WHEN 2 THEN ', ' + col2 ELSE '' END ) + 
    MAX(CASE seq WHEN 3 THEN ', ' + col2 ELSE '' END ) +
    MAX(CASE seq WHEN 4 THEN ', ' + col2 ELSE '' END ) +
    MAX(CASE seq WHEN 5 THEN ',...' ELSE '' END ) 
    as col2
from (
    select col1, col2, ROW_NUMBER() OVER ( PARTITION BY col1 ORDER BY col2 ) seq
    from @t
    group by col1, col2
) x
group by col1

Answer 15

STRING_AGG ( expression, separator ) [ <order_clause> ]

<order_clause> ::=   
    WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] )

I came to Stackoverflow looking for the SQL server string aggregate function.

The relevant question had been closed, marked as a duplicate of this question, and so I am forced to answer it here or not at all.

See https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017 for details.