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