SQL Server CLR function to concatenate values in a column

In one of my earlier post Creating a Delimited list from a column of a table, we had seen how to generate CSV (demilited string) from column values in a table using XML PATH and COALESCE method. These methods though solved our problem, but are not generic.

In this post we will look at how to generalize the solution by using SQLCLR aggregates.

Before we look at the actual CLR aggregate let’s look at what a SQLCLR aggregate comprises of. A SQLCLR Aggregate is defined as a STRUCTURE in .NET. It consists of 4 methods

  1. public void Init( )
    Before the query processor starts the group aggregate computation, this method is invoked to initialize the group aggregate value
  2. public void Accumulate(input_type value)
    For each value in the group being accumulated, the query processor invokes this method for accumulating the values. Input_type is the managed SQL SERVER data type.
  3. public void Merge(udagg_struct value)
    In case the query processor computes partial aggregations within a group, this method is invoked to merge the partial computations.
  4. public return_type Terminate( )
    This method is finally invoked which completes the aggregation computation and returns the result. Return_type type is the managed SQL SERVER data type.

Now lets Look at our CLR code. Open VISUAL STUDIO and create a new project under Visual C#-> Database and name it as SQLGenerateDelimitedString. Then add a new item of type aggregate to the solution naming it as Generate_CSV. In the new item, put the following code.


Once you create the CLR, build the solution.

After you build the solution, next step is to deploy the CLR. Open the SQL Server Management Studio and run the following queries.

  1. Make sure the server is clr enabled. If not run the following script to enable clr.

2. Create the assembly


WITH PERMISSION_SET = SAFE;
GO

3. Create the aggregate


4. To test the aggregate


 

Leave a Reply

Your email address will not be published. Required fields are marked *