If you tried to concatenate strings from group in SQL, probably you would start looking for an aggregate function similar to SUM or COUNT. There is no function like this in SQL Server 2005 and even further there is no way how to create a custom aggregate function using SQL. You can use just built-in aggregates.
Fortunately, SQL Server 2005 integrates common language runtime (CLR). So, the solution is to write an aggregate function in C# and include it among built-in functions in SQL Server.
Let us show an example with aggregate function for strings concatenation. At first you should create a new project in VS - use template Class Library, name it for example MyCLRLib.
The key methods are:
Init()- initializes private variables
Accumulate()- appends the next value
Merge()- merges partial aggregates
Terminates()- returns a result aggregation
Now you can register this function in SQL Server. First of all, enable .NET CLR code execution in your SQL Server:
and then create new aggregate function from your compiled assembly:
Now you can write: