1

What kind of SQL is needed to group by GroupID and then concatonate all of the Name values into 1 row with comma's separating the values?

So this:

GroupName   GroupID Name
All screens 139091  Business
All screens 139091  Business 
All screens 139091  Cafeteria/Bathrooms
All screens 139091  Lobby

Would become this:

GroupName   GroupID Name
All screens 139091  Business, Cafeteria/Bathrooms, Lobby

This would need to be done on as many GroupID's as there are in the table and I am guessing skip duplicates. Is this better done on the C# side? The SQL version is 2008 R2

Group_Concat does not exist for use in SQL Server

JoJo
  • 4,643
  • 9
  • 42
  • 65
  • 3
    Google: "SQL Server aggregate string concatenation". – Gordon Linoff Feb 06 '16 at 13:19
  • 1
    [Concatenating Row Values in Transact-SQL](https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/) The most common one is XML + STUFF (to avoid duplicates use `DISTINCT` in subquery) – Lukasz Szozda Feb 06 '16 at 13:22

1 Answers1

2

Hope this will help you out.

   SELECT GroupName, GROUPID , STUFF(( SELECT ',' + NAME FROM #TAB FOR XML PATH('')),1,1,'')
   FROM #TAB GROUP BY GROUPNAME, GROUPID
Shakeer Mirza
  • 5,054
  • 2
  • 18
  • 41
  • 1
    You need to add a `WHERE` clause to your nested query... this is not going to work as expected otherwise when you have different group IDs/names. – Lucero Feb 06 '16 at 13:40
  • @Lucero What would the WHERE criteria need to be? – JoJo Feb 06 '16 at 13:48
  • 1
    @JoJo Probably GroupID would the column you want to JOIN on: `SELECT T1.GroupName, T1.GroupID, STUFF((SELECT ','+T2.NAME FROM #TAB T2 WHERE T1.GroupID=T2.GroupID FOR XML PATH('')),1,1,'') FROM #TAB T1 GROUP BY T1.GroupName, T1.GroupID` – Lucero Feb 06 '16 at 17:20