TSQL: Concatenate Column from Multiple Rows into String
Ok , So I had a problem related to concatenation of a single Column into one ( same ) column when using GROUP BY .
The Problem came from a Reporting Services perspective and there might be other solutions ( like coding in VB.NET in the report )
I have found a very nice samples :
Option 1:
use Northwind
declare @CategoryList varchar(1000)
set @CategoryList =''
select @CategoryList = @CategoryList + ' , ' + CategoryName from Categories
select 'Results = ' + @CategoryList
Option 2:
use Northwind
declare @CategoryList varchar(1000)
select @CategoryList = coalesce(@CategoryList + ', ', '') + CategoryName from Categories
select 'Results = ' + @CategoryList
In the end I used option 2 as part of a UserDefiendFunction .
The manipulation was that the of the “GROUP BY” was sent to the UDF to use in it’s select “where” , so it could return the filtered row concatenated. And that UDF was called as part of the grand SELECT.
NOTE: this is a developer solution , a DBA might have a better solution ;)