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.

using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
    Format.UserDefined, /// Binary Serialization because of StringBuilder
    IsInvariantToOrder = false, /// order changes the result
    IsInvariantToNulls = true,  /// nulls don't change the result
    IsInvariantToDuplicates = false, /// duplicates change the result
    MaxByteSize = 8000
)]
public struct Generate_CSV : IBinarySerialize
{
    // This is a place-holder member field
    private StringBuilder _accumulator;
    private string _delimiter;
    public Boolean IsNull { get; private set; }
    public void Init()
    {
        // Put your code here
        _accumulator = new StringBuilder();
        _delimiter = ",";
        this.IsNull = true;
    }
    public void Accumulate(SqlString Value)
    {
        if (_accumulator.Length > 0) _accumulator.Append(_delimiter);
        _accumulator.Append(Value.Value);
        if (Value.IsNull == false) this.IsNull = false;
    }
    public void Merge(Generate_CSV Group)
    {
        if (_accumulator.Length > 0 & Group._accumulator.Length > 0)
            _accumulator.Append(_delimiter);
        // Put your code here
        this._accumulator.Append(Group._accumulator);
    }
    public SqlString Terminate()
    {
        // Put your code here
        return new SqlString(_accumulator.ToString());
    }
    /// <summary>
    /// deserialize from the reader to recreate the struct
    /// </summary>
    /// <param name="r">BinaryReader</param>
    void IBinarySerialize.Read(System.IO.BinaryReader r)
    {
        _accumulator = new StringBuilder(r.ReadString());

        if (_accumulator.Length != 0) this.IsNull = false;
    }
    /// <summary>
    /// searialize the struct.
    /// </summary>
    /// <param name="w">BinaryWriter</param>
    void IBinarySerialize.Write(System.IO.BinaryWriter w)
    {
        w.Write(_accumulator.ToString());
    }
}

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.

exec sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
exec sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

2. Create the assembly

CREATE Assembly SQLGenerateDelimitedString
   AUTHORIZATION dbo
   FROM'C:\Packages\SQLGenerateDelimitedString\SQLGenerateDelimitedString\
   bin\Debug\SqlC

WITH PERMISSION_SET = SAFE;
GO

3. Create the aggregate

CREATE AGGREGATE dbo.Generate_CSV (
    @Value NVARCHAR(MAX)
) RETURNS NVARCHAR(MAX)
EXTERNAL Name SQLGenerateDelimitedString.Generate_CSV;
GO

4. To test the aggregate

IF  EXISTS (SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'[dbo].[Photo_Tags]')
                    AND type in (N'U')
            )
BEGIN
    DROP TABLE [dbo].[Photo_Tags]
END
GO
CREATE TABLE [dbo].[Photo_Tags]
(
    Photo_ID    INT
    ,Tag        VARCHAR(256)
)
GO
INSERT INTO [dbo].[Photo_Tags] (Photo_ID,Tag) SELECT 1,'Beach'
INSERT INTO [dbo].[Photo_Tags] (Photo_ID,Tag) SELECT 1,'Sand'
INSERT INTO [dbo].[Photo_Tags] (Photo_ID,Tag) SELECT 2,'Mountain'
INSERT INTO [dbo].[Photo_Tags] (Photo_ID,Tag) SELECT 2,'Waterfall'
INSERT INTO [dbo].[Photo_Tags] (Photo_ID,Tag) SELECT 3,'Island'
INSERT INTO [dbo].[Photo_Tags] (Photo_ID,Tag) SELECT 3,'White Sand'
INSERT INTO [dbo].[Photo_Tags] (Photo_ID,Tag) SELECT 3,'Blue waters'
SELECT Photo_ID,dbo.Generate_CSV(Tag)
FROM Photo_Tags
GROUP BY Photo_ID