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

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 2

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 3

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 4

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 5

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://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017 for details.

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,