SQL Server Aggregate to concatenate strings

Aggregate_Concatenate.sql

-- This script deploys the dbo.Concatenate() aggregate function on SQL Server. This is compiled from the code Concatenate.cs below.

CREATE ASSEMBLY [concat]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300E3EE2A540000000000000000E00002210B010B00000C00000006000000000000AE2B0000002000000040000000000010002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000602B00004B000000004000009003000000000000000000000000000000000000006000000C000000282A00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000B40B000000200000000C000000020000000000000000000000000000200000602E72737263000000900300000040000000040000000E0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001200000000000000000000000000004000004200000000000000000000000000000000902B0000000000004800000002000500B421000074080000010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000013300100110000000100001100027B010000046F0E00000A0A2B00062A360002730F00000A7D010000042A00133002002500000002000011000F01281000000A16FE010A062D022B13027B010000040F01281100000A6F1200000A262A000000133002002100000002000011001403FE0116FE010A062D022B12027B01000004037B010000046F1300000A262A000000133004004D00000003000011007E1400000A0A027B010000042C13027B010000046F1500000A16FE0216FE012B0117000C082D1A027B0100000416027B010000046F1500000A17596F1600000A0A06731700000A0B2B00072A000000133002002900000002000011000314FE0116FE010A062D0B7201000070731800000A7A02036F1900000A731A00000A7D010000042A000000133002002A00000002000011000314FE0116FE010A062D0B720F000070731800000A7A03027B010000046F0E00000A6F1B00000A002A1E02281C00000A2A000042534A4201000100000000000C00000076322E302E35303732370000000005006C000000D8020000237E0000440300007803000023537472696E677300000000BC0600002000000023555300DC060000100000002347554944000000EC0600008801000023426C6F6200000000000000020000015717A2010900000000FA253300160000010000001700000002000000010000000800000004000000010000001C0000000B00000003000000010000000100000001000000010000000200000000000A00010000000000060031002A000A005F00440006007C0070000A00CF00BA000600FE00F40006001001F40006006801560106007F01560106009C0156010600B50156010600CE0156010600E901560106000202560106001F025601060051023E023F00650200000600940274020600B40274020600D9022A000A00EF0244000A001003440006003C032A00060054032A000000000001000000000001000100012010001500000005000100010001008A000A0050200000000083089E000E0001006D20000000008600B500120001007C20000000008600D90016000100B020000000008600E4001C000200E020000000008600EA00220003003C2100000000E6010B0127000300742100000000E6011D012D000400AA21000000008618230112000500000001003C01000001004201000001004801000001004F0102000900390023013700410023013700490023013700510023013700590023013700610023013700690023013700710023013700790023013C00890023014200910023011200990023011200A10023014700090017030E0019002301120021002003C60021002B030E0019003503CA0019003503D400B1004303DA0019004903DD0019001703E100210023013700B9002301370029006A030E0019002301370031001D0137000900230112002E002B002D012E005B0066012E001300FA002E001B002D012E002300EE002E000B00EE002E003B003C012E00430049012E004B0054012E0053005D0143006B004D00C200D000E70002000100000029013300020001000300048000000100000000000000000000000000D20200000200000000000000000000000100210000000000020000000000000000000000010038000000000000000000003C4D6F64756C653E00636F6E6361742E646C6C00436F6E636174656E617465006D73636F726C69620053797374656D004F626A6563740053797374656D2E44617461004D6963726F736F66742E53716C5365727665722E536572766572004942696E61727953657269616C697A650053797374656D2E5465787400537472696E674275696C646572005F696E7465726D656469617465526573756C74006765745F496E7465726D656469617465526573756C7400496E69740053797374656D2E446174612E53716C54797065730053716C537472696E6700416363756D756C617465004D65726765005465726D696E6174650053797374656D2E494F0042696E61727952656164657200526561640042696E617279577269746572005772697465002E63746F7200496E7465726D656469617465526573756C740076616C7565006F7468657200726561646572007772697465720053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7956657273696F6E41747472696275746500417373656D626C7946696C6556657273696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C69747941747472696275746500636F6E6361740053657269616C697A61626C654174747269627574650053716C55736572446566696E656441676772656761746541747472696275746500466F726D617400546F537472696E67006765745F49734E756C6C006765745F56616C756500417070656E6400537472696E6700456D707479006765745F4C656E67746800417267756D656E744E756C6C457863657074696F6E0052656164537472696E6700000000000D720065006100640065007200000D7700720069007400650072000000000049F527D6E78DF44B9985349EF50A43450008B77A5C561934E0890306120D0320000E0320000105200101111105200101120804200011110520010112150520010112190328000E042001010E05200101114104200101080520010111557401000200000005005402124973496E76617269616E74546F4E756C6C73015402174973496E76617269616E74546F4475706C696361746573005402124973496E76617269616E74546F4F726465720054080B4D61784279746553697A65401F0000540E044E616D650B436F6E636174656E6174650307010E03200002052001120D0E03070102052001120D1C02060E032000080520020E08080607030E1111020B010006636F6E63617400003201002D412053514C205365727665722041676772656761746520746F20636F6E636174656E61746520737472696E677300000E010009466F78547269636B7300000C010007312E302E302E3000000A010005446562756700000801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100000000000000E3EE2A5400000000020000001C010000442A0000440C00005253445349772B2144A9E0409A276A1BD7102D4202000000633A5C55736572735C66696C69702E6465766F735C446F63756D656E74735C436F64655C74726173685C636F6E6361745C636F6E6361745C6F626A5C44656275675C636F6E6361742E70646200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000882B000000000000000000009E2B0000002000000000000000000000000000000000000000000000902B00000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000380300000000000000000000380334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00498020000010053007400720069006E006700460069006C00650049006E0066006F00000074020000010030003000300030003000340062003000000074002E00010043006F006D006D0065006E0074007300000041002000530051004C0020005300650072007600650072002000410067006700720065006700610074006500200074006F00200063006F006E0063006100740065006E00610074006500200073007400720069006E0067007300000034000A00010043006F006D00700061006E0079004E0061006D0065000000000046006F00780054007200690063006B0073000000380007000100460069006C0065004400650073006300720069007000740069006F006E000000000063006F006E0063006100740000000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E003000000038000B00010049006E007400650072006E0061006C004E0061006D006500000063006F006E006300610074002E0064006C006C000000000038000A0001004C006500670061006C0043006F007000790072006900670068007400000046006F00780054007200690063006B007300000040000B0001004F0072006900670069006E0061006C00460069006C0065006E0061006D006500000063006F006E006300610074002E0064006C006C0000000000300007000100500072006F0064007500630074004E0061006D0065000000000063006F006E0063006100740000000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E003000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000B03B00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE
GO

CREATE AGGREGATE [dbo].[Concatenate] (@value [nvarchar](max))
RETURNS [nvarchar](max)
EXTERNAL NAME [concat].[Concatenate]
GO

Concatenate.cs

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

[Serializable]
[SqlUserDefinedAggregate(Format.UserDefined, IsInvariantToNulls = true, IsInvariantToDuplicates = false, IsInvariantToOrder = false, MaxByteSize = 8000, Name = "Concatenate")]
public class Concatenate : IBinarySerialize
{
    private StringBuilder _intermediateResult;

    internal string IntermediateResult {
        get
        {
            return _intermediateResult.ToString();
        } 
    }

    public void Init()
    {
        _intermediateResult = new StringBuilder();
    }

    public void Accumulate(SqlString value)
    {
        if (value.IsNull) return;
        _intermediateResult.Append(value.Value);
    }

    public void Merge(Concatenate other)
    {
        if (null == other)
            return;

        _intermediateResult.Append(other._intermediateResult);
    }

    public SqlString Terminate()
    {
        var output = string.Empty;

        if (_intermediateResult != null && _intermediateResult.Length > 0)
            output = _intermediateResult.ToString(0, _intermediateResult.Length - 1);

        return new SqlString(output);
    }

    public void Read(BinaryReader reader)
    {
        if (reader == null) 
            throw new ArgumentNullException("reader");
        
        _intermediateResult = new StringBuilder(reader.ReadString());
    }

    public void Write(BinaryWriter writer)
    {
        if (writer == null) 
            throw new ArgumentNullException("writer");
        
        writer.Write(_intermediateResult.ToString());
    }
}

ConcatenateTestScript.sql

create table test(
  id int identity(1,1) not null
    primary key
, class tinyint not null
, name nvarchar(120) not null )

insert into test values 
(1, N'This'),
(1, N'is'),
(1, N'just'),
(1, N'a'),
(1, N'test'),
(2, N','),
(3, N'do'),
(3, N'not'),
(3, N'be'),
(3, N'alarmed'),
(3, N','),
(3, N'this'),
(3, N'is'),
(3, N'just'),
(3, N'a'),
(3, N'test')


select dbo.Concatenate(name + ' ')
from test
group by class

drop table test

SOLVED: It seems I had a problem when using the function on large varchars...

Msg

6522, Level 16, State 2, Line 5
A .NET Framework error occurred during execution of user-defined routine or aggregate "Concatenate":
System.Data.SqlTypes.SqlTypeException: The buffer is insufficient. Read or write operation failed.
System.Data.SqlTypes.SqlTypeException:
at System.Data.SqlTypes.SqlBytes.Write(Int64 offset, Byte[] buffer, Int32 offsetInBuffer, Int32 count)
at System.Data.SqlTypes.StreamOnSqlBytes.Write(Byte[] buffer, Int32 offset, Int32 count)
at System.IO.BinaryWriter.Write(String value)
at Concatenate.Write(BinaryWriter writer)
.
Warning: Null value is eliminated by an aggregate or other SET operation.

---

The solution: one needs to enable CLR on the relevant DB - see SO answer here