SQL Server Aggregate to concatenate strings - shim for STRING_AGG

STRING_AGG.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 = -1,
    Name = "STRING_AGG"
)]
public class STRING_AGG : IBinarySerialize
{
    /// <summary>
    /// Stores the concatenated string
    /// </summary>
    public StringBuilder Result { get; private set; }

    /// <summary>
    /// Store the Separator
    /// </summary>
    public String Separator { get; private set; }

    /// <summary>
    /// Appends the Separator if necessary
    /// </summary>
    private void AddSeparator()
    {
        if (Result.Length > 0)
            Result.Append(Separator);
    }

    /// <summary>
    /// Initializes values for each group
    /// </summary>
    public void Init()
    {
        Result = new StringBuilder();
        Separator = string.Empty;
    }

    /// <summary>
    /// Stores separator in instance and appends value to the Result
    /// </summary>
    public void Accumulate(SqlString value, SqlString separator)
    {
        if (!separator.IsNull)
            Separator = separator.Value;

        if (value.IsNull)
            return;

        AddSeparator();
        Result.Append(value.Value);
    }

    /// <summary>
    /// Is called when parallelism is involved
    /// </summary>
    public void Merge(STRING_AGG group)
    {
        if (group.Result.Length > 0)
            return;

        AddSeparator();
        Result.Append(group.Result);
    }

    /// <summary>
    /// Completes the aggregate and returns the SqlString
    /// </summary>
    public SqlString Terminate()
    {
        //return new SqlString(_intermediateResult == null ? string.Empty : _intermediateResult.ToString());
        return new SqlString(Result.ToString());
    }

    #region IBinarySerialize
    /// <summary>
    /// Reads the values from the serialized stream
    /// </summary>
    /// <param name="reader">The BinaryReader</param>
    public void Read(BinaryReader reader)
    {
        if (reader == null)
            throw new ArgumentNullException("Serialized Data is NULL");

        Result = new StringBuilder(reader.ReadString());
        Separator = reader.ReadString();
    }

    /// <summary>
    /// Writes the values to the stream in order to be stored in serialized form
    /// </summary>
    /// <param name="writer">The BinaryWriter</param>
    public void Write(BinaryWriter writer)
    {
        if (writer == null)
            throw new ArgumentNullException("Serialization Writer is NULL");

        writer.Write(Result.ToString());
        writer.Write(Separator);
    }
    #endregion IBinarySerialize
}

STRING_AGG.sql

-- Enable CLR
/*
    EXEC sp_configure 'clr enabled', 1
    RECONFIGURE
*/

DECLARE @ver nvarchar(128) = CAST(serverproperty('ProductVersion') AS nvarchar)
SET @ver = SUBSTRING(@ver, 1, CHARINDEX('.', @ver) - 1)
IF (@ver < 14) -- only until Server 2017
BEGIN
	IF  EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'STRING_AGG') AND type = N'AF')
		DROP AGGREGATE [STRING_AGG]
	IF EXISTS(select 1 from sys.assemblies a WHERE name = N'STRING_AGG')
		DROP ASSEMBLY [STRING_AGG]

	CREATE ASSEMBLY [STRING_AGG]
		-- FROM  N'.\STRING_AGG.dll'
		-- compiled with .NET 2.0 / SQL Server 2014
		FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C01030075E1685A0000000000000000E00002210B010B00000E000000060000000000006E2D0000002000000040000000000010002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000202D00004B000000004000001803000000000000000000000000000000000000006000000C000000E82B00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000740D000000200000000E000000020000000000000000000000000000200000602E7273726300000018030000004000000004000000100000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001400000000000000000000000000004000004200000000000000000000000000000000502D000000000000480000000200050084210000640A000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001E027B010000042A2202037D010000042A1E027B020000042A2202037D020000042A860228010000066F1300000A1631120228010000060228030000066F1400000A262A5E02731500000A2802000006027E1600000A28040000062AEA0F02281700000A2D0D020F02281800000A28040000060F01281700000A2C012A0228050000060228010000060F01281800000A6F1400000A262AA2036F010000066F1300000A1631012A022805000006022801000006036F010000066F1900000A262A460228010000066F1A00000A731B00000A2AB2032D0B7201000070731C00000A7A02036F1D00000A731E00000A280200000602036F1D00000A28040000062AB2032D0B7231000070731C00000A7A030228010000066F1A00000A6F1F00000A030228030000066F1F00000A2A1E02282000000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C000000A0030000237E00000C0400005004000023537472696E6773000000005C0800006C00000023555300C8080000100000002347554944000000D80800008C01000023426C6F6200000000000000020000015717A0010900000000FA253300160000010000001C00000002000000020000000C00000007000000010000002000000014000000010000000200000004000000010000000200000000000A00010000000000060034002D000A006200470006007F0073000A00E600D100060015010B01060027010B010600BA01A8010600D101A8010600EE01A80106000D02A80106002602A80106003F02A80106005A02A80106007502A8010600AD028E020600C1028E020600CF02A8010600E802A8010600180305034F002C03000006005B033B0306007B033B03060099032D000A00AF0347000A00D00347000600D7033B03060004042D0006002F042D000000000001000000000001000100012010001900000005000100010001004001410001005801450050200000000086088D000A000100582000000000810898000F0001006120000000008608A300150002006920000000008108B100190002007220000000008100BF001E0003009420000000008600CC001E000300AC20000000008600F00022000300E720000000008600FB002A0005001021000000008600010130000600222100000000E6012201350006004F2100000000E60134013B0007007C210000000086183A011E000800000001008401000001008401000001008401000002008A01000001009401000001009A0100000100A1010200090039003A01190041003A01190049003A01190051003A01190059003A01190061003A01190069003A01190071003A01190079003A01510081003A01190089003A01190091003A01190099003A015600A9003A015C00B1003A011E00B9003A011E00C1003A016100D1003A011E001900F203E0001900FD03E40019003A011E00D9000B04450021001104EA0021001C0415001900FD03EE0009002604150021003A011900E1003A01190029004504150019003A01190031003401190009003A011E0020009300DB0021009300DB002E001B0004012E002B00F4002E007B006B012E0033000A012E00730062012E000B00F4002E00130004012E00230004012E003B0004012E004B0004012E00530022012E0063004C012E006B00590140009300DB0041009300DB0043008B00670060009300DB0080009300DB000200010000007301480000007A014D0002000100030001000200030002000300050001000400050004800000010000000000000000000000000019000000020000000000000000000000010024000000000002000000000000000000000001003B00000000000000003C4D6F64756C653E00535452494E475F4147472E646C6C00535452494E475F414747006D73636F726C69620053797374656D004F626A6563740053797374656D2E44617461004D6963726F736F66742E53716C5365727665722E536572766572004942696E61727953657269616C697A650053797374656D2E5465787400537472696E674275696C646572006765745F526573756C74007365745F526573756C74006765745F536570617261746F72007365745F536570617261746F7200416464536570617261746F7200496E69740053797374656D2E446174612E53716C54797065730053716C537472696E6700416363756D756C617465004D65726765005465726D696E6174650053797374656D2E494F0042696E61727952656164657200526561640042696E617279577269746572005772697465002E63746F72003C526573756C743E6B5F5F4261636B696E674669656C64003C536570617261746F723E6B5F5F4261636B696E674669656C6400526573756C7400536570617261746F720076616C756500736570617261746F720067726F757000726561646572007772697465720053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C65417474726962757465004775696441747472696275746500417373656D626C7956657273696F6E41747472696275746500417373656D626C7946696C6556657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053657269616C697A61626C654174747269627574650053716C55736572446566696E656441676772656761746541747472696275746500466F726D617400436F6D70696C657247656E657261746564417474726962757465006765745F4C656E67746800417070656E6400537472696E6700456D707479006765745F49734E756C6C006765745F56616C756500546F537472696E6700417267756D656E744E756C6C457863657074696F6E0052656164537472696E6700002F530065007200690061006C0069007A00650064002000440061007400610020006900730020004E0055004C004C000039530065007200690061006C0069007A006100740069006F006E00200057007200690074006500720020006900730020004E0055004C004C00000089908C5CFD779349B11978061F4B47630008B77A5C561934E089042000120D05200101120D0320000E042001010E03200001072002011111111105200101120804200011110520010112150520010112190306120D02060E042800120D0328000E042001010205200101115104200101080520010111657301000200000005005402124973496E76617269616E74546F4E756C6C73015402174973496E76617269616E74546F4475706C696361746573005402124973496E76617269616E74546F4F726465720054080B4D61784279746553697A65FFFFFFFF540E044E616D650A535452494E475F414747040100000003200008052001120D0E03200002052001120D1C0F01000A535452494E475F414747000005010000000017010012436F7079726967687420C2A920203230313800002901002462383662336562612D376332332D343130302D626663332D63623765313238643330373200000C010007312E302E302E3000000801000200000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100000000000075E1685A00000000020000001C010000042C0000040E0000525344539056BBA08E79A14BBE41BC440F1FDF3A07000000633A5C55736572735C4265686D6F5C446F63756D656E74735C56697375616C2053747564696F20323031325C50726F6A656374735C535452494E475F4147475C535452494E475F4147475C6F626A5C52656C656173655C535452494E475F4147472E706462000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000482D000000000000000000005E2D0000002000000000000000000000000000000000000000000000502D00000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500200010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000C00200000000000000000000C00234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00420020000010053007400720069006E006700460069006C00650049006E0066006F000000FC010000010030003000300030003000340062003000000040000B000100460069006C0065004400650073006300720069007000740069006F006E000000000053005400520049004E0047005F0041004700470000000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E003000000040000F00010049006E007400650072006E0061006C004E0061006D006500000053005400520049004E0047005F004100470047002E0064006C006C00000000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A900200020003200300031003800000048000F0001004F0072006900670069006E0061006C00460069006C0065006E0061006D006500000053005400520049004E0047005F004100470047002E0064006C006C000000000038000B000100500072006F0064007500630074004E0061006D0065000000000053005400520049004E0047005F0041004700470000000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E003000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000703D00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
		WITH PERMISSION_SET = SAFE
		
	CREATE AGGREGATE [STRING_AGG] (@value [nvarchar](max), @separator [nvarchar](max))
		RETURNS [nvarchar](max)
		EXTERNAL NAME [STRING_AGG].[STRING_AGG]
		
	IF EXISTS(SELECT 1 FROM sys.configurations WHERE name = 'clr enabled' and value = 0)
	BEGIN
		EXEC sp_configure 'clr enabled', 1
		RECONFIGURE
	END
END

test.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 class, dbo.STRING_AGG(name, ' ')
from #test
group by class

drop table #test