Concatenate Aggregate Function For SQL Server

Tired of the fact that, still after many years, there isn’t a convenient and built-in way to concatenate strings in an aggegrating query. Well, wait no more!

First, check if your database environment is setup to allow CLR user-defined functions:

SELECT * FROM sys.configurations WHERE name = 'clr enabled'

If value_in_use = 1, you are setup for it. If not, you can turn it on yourself via:

sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

Now, fire up VS2010. Create a Visual C# SQL CLR Database Project from the SQL Server database templates. Right-click on the project in the solution and click on “Add New Item”. From the list, select “Aggregate” (and note all the other goodies you could create). I called the solution/project `ConcatMaxNullable`.

There is plenty of good information on what the various methods stubs are doing. Also, there are some hard-to-find example of concatenate aggregates out there too. Unfortunately, none of them fit my needs. I need one that:

• Concatenated strings. (Duh!)
• Could output to the new nvarchar(max) type and not be limited by the 8000 chars of the old nvarchar().
• Allowed skipping nulls, or replace them with empty strings still delimited.

So, I rolled my own. I’m going to go ahead and simply post my own code for others to use. Should be self-explanatory as the methods don’t really do anything particularly novel.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
  Format.UserDefined,
  IsInvariantToOrder = false,
  IsInvariantToNulls = true,
  IsInvariantToDuplicates = false,
  MaxByteSize = -1
  )]
public struct ConcatMaxNullable : IBinarySerialize
{
  private StringBuilder _accumulator;
  private string _delimiter;

  public Boolean IsNull { get; private set; }

  public void Init()
  {
    _accumulator = new StringBuilder();
    _delimiter = string.Empty;
    this.IsNull = true;
  }

  public void Accumulate(SqlChars item, SqlString delimiter, SqlBoolean skipNulls)
  {
    // if value is null, return if skipNulls is true
    if (item.IsNull && skipNulls.IsTrue)
      return;

    // if we have an actual delimiter
    if ((!delimiter.IsNull) && (delimiter.Value.Length > 0))
    {
      _delimiter = delimiter.Value;
      // accumulate delimiter if we have something already
      if (_accumulator.Length > 0)
        _accumulator.Append(delimiter.Value);
    }

    // if value is null, just add delimiter (above) and return
    if (item.IsNull)
      return;
    else
    {
      _accumulator.Append(item.Value);
      this.IsNull = false;
    }

  }

  public void Merge(ConcatMaxNullable Group)
  {

    if (_accumulator.Length > 0 && Group._accumulator.Length > 0)
      _accumulator.Append(_delimiter);

    _accumulator.Append(Group._accumulator.ToString());

  }

  public SqlChars Terminate()
  {

    return new SqlChars(_accumulator.ToString());

  }

  void IBinarySerialize.Read(System.IO.BinaryReader r)
  {

    _delimiter = r.ReadString();
    _accumulator = new StringBuilder(r.ReadString());

    if (_accumulator.Length != 0)
      this.IsNull = false;

  }

  void IBinarySerialize.Write(System.IO.BinaryWriter w)
  {

    w.Write(_delimiter);
    w.Write(_accumulator.ToString());

  }

}

Once you pasted this in, compile and then get the DLL somewhere the server can see it. Installing it is as easy as running these two little scripts:

CREATE ASSEMBLY ConcatMaxNullable FROM 'C:\\ConcatMaxNullable.dll'
WITH PERMISSION_SET=SAFE
GO

CREATE AGGREGATE [dbo].[ConcatMaxNullable]
(@item [nvarchar](max), @delimiter [nvarchar](8), @skipNulls bit)
RETURNS[nvarchar](max)
EXTERNAL NAME [ConcatMaxNullable].[ConcatMaxNullable]
GO

Then, you can easily do something like this query, which shows you your foreign keys, and the columns involved, in a comma-separated format ready for scripting.

SELECT
  c.[TABLE_NAME], k.[CONSTRAINT_NAME], dbo.ConcatMaxNullable([COLUMN_NAME],',',1) AS [Cols] 
FROM
  INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
INNER JOIN
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE k ON c.[CONSTRAINT_NAME] = k.[CONSTRAINT_NAME] 
WHERE
  c.[CONSTRAINT_TYPE] = 'FOREIGN KEY'
GROUP BY
  c.[TABLE_NAME], k.[CONSTRAINT_NAME]
ORDER BY
  c.[TABLE_NAME], k.[CONSTRAINT_NAME] 

Enjoy!