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!
Paul Tiseo's Livin' La Vida CIO 