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!
Man, YOU ARE A LEGEND. This is awesome stuff, thank you.
I’ve been fighting with all the other hacks but this is the bees knees.
Works perfectly.
Thanks,
Pat.