SQL Server Aggregate to concatenate DISTINCT strings

Aggregate_Concatenate.sql

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

CREATE ASSEMBLY [concat_distinct]
  --FROM  'C:\<your_compiled_assembly>.dll' 
  FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C01030095B0EC560000000000000000E00002210B010B000010000000060000000000001E2F0000002000000040000000000010002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000CC2E00004F000000004000003803000000000000000000000000000000000000006000000C000000942D00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000240F0000002000000010000000020000000000000000000000000000200000602E7273726300000038030000004000000004000000120000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001600000000000000000000000000004000004200000000000000000000000000000000002F0000000000004800000002000500B0220000E40A0000010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000013300100110000000100001100027B010000046F1200000A0A2B00062A360002731300000A7D010000042A00133002003B00000002000011000F01281400000A16FE010A062D022B29027B010000040F01281500000A6F1600000A0A062D13027B010000040F01281500000A6F1700000A002A001B3002005100000003000011001403FE0116FE010B072D022B4200037B010000046F1800000A0C2B16086F1900000A0A00027B01000004066F1700000A0000086F1A00000A0B072DE0DE100814FE010B072D07086F1B00000A00DC002A0000000110000002001B00243F001000000000133002004000000004000011007E1C00000A0A027B010000042C13027B010000046F1D00000A16FE0216FE012B0117000C082D0D02027B0100000428080000060A06731E00000A0B2B00072A133002006D00000005000011000314FE0116FE010B072D0B7201000070731F00000A7A036F2000000A0A0614FE0116FE010B072D0B720F000070732100000A7A027B0100000414FE0116FE010B072D0B722D000070732100000A7A027B01000004066F1600000A0B072D0D027B01000004066F1700000A002A000000133003002B00000002000011000314FE0116FE010A062D0B724D000070731F00000A7A0302027B0100000428080000066F2200000A002A001B300200490000000600001100725B0000700A00036F1800000A0D2B11096F1900000A0B000607282300000A0A00096F1A00000A130411042DE3DE120914FE01130411042D07096F1B00000A00DC00060C2B00082A0000000110000002000F0021300012000000004E02731300000A7D0100000402282400000A002A42534A4201000100000000000C00000076322E302E35303732370000000005006C00000080030000237E0000EC030000A804000023537472696E677300000000940800006000000023555300F408000010000000234755494400000004090000E001000023426C6F6200000000000000020000015717A2090900000000FA25330016000001000000220000000200000001000000090000000500000001000000240000000E0000000600000001000000010000000100000004000000010000000200000000000A0001000000000006003D0036000A006B005000060097007C000A00D700C20006000601FC0006001801FC0006007F016D01060096016D010600B3016D010600D2016D010600EB016D01060004026D0106001F026D0106003A026D01060072025302060086025302060094026D010600AD026D010600DD02CA024F00F102000006002003000306004003000306006E0336000A00840350000A00A50350000600B5037C000600D1037C000600EC037C000600FA037C0006003504220406004A04360006005E0436000600750436000600960436000000000001000000000001000100012010001E00000005000100010001009F000A005020000000008308A600110001006D20000000008600BD00150001007C20000000008600E10019000100C420000000008600EC001F0002003421000000008600F20025000300802100000000E60113012A000300FC2100000000E60125013000040034220000000081002B01360005009C22000000008618350115000600000001004E01000001005401000001005A0100000100610100000100680102000900390035014300410035014300490035014300510035014300590035014300610035014300690035014300710035014300790035014800810035014300890035014300910035014300990035014D00A90035015300B10035011500B90035011500C100350158000900AC0311000C00350115002100BC03E0002100C70311001400DF03EA001400E803F0001C0008040001240016040F01F1004104E000F90056041500010165041E0114006B04210121003501430009013501430029008B0411001101350143003100250143000101A00431010900350115002E0033005E012E000B0043012E00130058012E001B0058012E00230058012E002B0043012E00530076012E003B0058012E004B0058012E006B00AD012E007B00BF012E006300A0012E007300B60143008B005E00D600F600140125012C0137010200010000003B013F00020001000300DA00E400FA0009010480000001000000000000000000000000005E03000002000000000000000000000001002D000000000002000000000000000000000001004400000000000000003C4D6F64756C653E00636F6E6361745F64697374696E63742E646C6C00436F6E63617444697374696E6374006D73636F726C69620053797374656D004F626A6563740053797374656D2E44617461004D6963726F736F66742E53716C5365727665722E536572766572004942696E61727953657269616C697A650053797374656D2E436F6C6C656374696F6E732E47656E6572696300494C6973746031005F6461746F73006765745F496E7465726D656469617465526573756C7400496E69740053797374656D2E446174612E53716C54797065730053716C537472696E6700416363756D756C617465004D65726765005465726D696E6174650053797374656D2E494F0042696E61727952656164657200526561640042696E617279577269746572005772697465004C697374546F537472002E63746F7200496E7465726D656469617465526573756C740076616C7565006F746865720072656164657200777269746572006C6973740053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C65417474726962757465004775696441747472696275746500417373656D626C7956657273696F6E41747472696275746500417373656D626C7946696C6556657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C69747941747472696275746500636F6E6361745F64697374696E63740053657269616C697A61626C654174747269627574650053716C55736572446566696E656441676772656761746541747472696275746500466F726D617400546F537472696E67004C6973746031006765745F49734E756C6C006765745F56616C75650049436F6C6C656374696F6E603100436F6E7461696E73004164640049456E756D657261626C6560310049456E756D657261746F72603100476574456E756D657261746F72006765745F43757272656E740053797374656D2E436F6C6C656374696F6E730049456E756D657261746F72004D6F76654E6578740049446973706F7361626C6500446973706F736500537472696E6700456D707479006765745F436F756E7400417267756D656E744E756C6C457863657074696F6E0052656164537472696E6700457863657074696F6E00436F6E6361740000000D720065006100640065007200001D6400610074006F002000650073007400E10020006E0075006C006F00011F6400610074006F0073002000650073007400E10020006E0075006C006F00010D7700720069007400650072000001000000000C8CC3193F67EC4280528A0D7EEB796D0008B77A5C561934E089060615120D010E0320000E0320000105200101111105200101120804200011110520010112150520010112190820010E15120D010E0328000E042001010E042001010205200101115104200101080520010111657701000200000005005402124973496E76617269616E74546F4E756C6C73015402174973496E76617269616E74546F4475706C696361746573005402124973496E76617269616E74546F4F726465720054080B4D61784279746553697A65401F0000540E044E616D650E436F6E63617444697374696E63740307010E05151269010E032000020515126D010E0520010213000520010113000307010205151271010E08200015127501130005151275010E04200013000907030E02151275010E02060E032000080607030E1111020407020E020500020E0E0E0B07050E0E0E151275010E021401000F636F6E6361745F64697374696E6374000005010000000017010012436F7079726967687420C2A920203230313600002901002430623865303338612D313933322D343739322D396135662D62396130366266653331336200000C010007312E302E302E3000000801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100000000000095B0EC5600000000020000001C010000B02D0000B00F0000525344531BD46CCBA630484D83BBE85EDB591DE40A000000663A5C70726F796563746F735C636F6E6361745F64697374696E63745C636F6E6361745F64697374696E63745C6F626A5C44656275675C636F6E6361745F64697374696E63742E706462000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000F42E000000000000000000000E2F0000002000000000000000000000000000000000000000000000002F0000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000E00200000000000000000000E00234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00440020000010053007400720069006E006700460069006C00650049006E0066006F0000001C0200000100300030003000300030003400620030000000480010000100460069006C0065004400650073006300720069007000740069006F006E000000000063006F006E006300610074005F00640069007300740069006E00630074000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E003000000048001400010049006E007400650072006E0061006C004E0061006D006500000063006F006E006300610074005F00640069007300740069006E00630074002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100360000005000140001004F0072006900670069006E0061006C00460069006C0065006E0061006D006500000063006F006E006300610074005F00640069007300740069006E00630074002E0064006C006C000000400010000100500072006F0064007500630074004E0061006D0065000000000063006F006E006300610074005F00640069007300740069006E00630074000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E00300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000203F00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
  WITH PERMISSION_SET = SAFE

CREATE AGGREGATE [dbo].[ConcatDistinct] (@value [nvarchar](max))
RETURNS [nvarchar](max)
EXTERNAL NAME [concat_distinct].[ConcatDistinct]

ConcatDistinct.cs

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

[Serializable]
[SqlUserDefinedAggregate(Format.UserDefined, IsInvariantToNulls = true, IsInvariantToDuplicates = false, IsInvariantToOrder = false, MaxByteSize = 8000, Name = "ConcatDistinct")]
public class ConcatDistinct : IBinarySerialize
{
    //private StringBuilder _intermediateResult;
    private IList<String> _datos = new List<String>();

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

    public void Init()
    {
        //_intermediateResult = new StringBuilder();
        _datos = new List<String>();
    }

    public void Accumulate(SqlString value)
    {
        if (value.IsNull) return;
        //_intermediateResult.Append(value.Value);
        if (!_datos.Contains(value.Value))
            _datos.Add(value.Value);
    }

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

        //_intermediateResult.Append(other._intermediateResult);

        foreach (String str in other._datos) {
            _datos.Add(str);
        }
    }

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

        /*if (_intermediateResult != null && _intermediateResult.Length > 0)
            output = _intermediateResult.ToString(0, _intermediateResult.Length - 1);*/
        if (_datos != null && _datos.Count > 0)
            output = ListToStr(_datos);
        return new SqlString(output);
    }

    public void Read(BinaryReader reader)
    {
        if (reader == null)
            throw new ArgumentNullException("reader");

        //_intermediateResult = new StringBuilder(reader.ReadString());
        String dato = reader.ReadString();

        if (dato == null)
            throw new Exception("dato está nulo");
        if (_datos == null)
            throw new Exception("datos está nulo");
        if (!_datos.Contains(dato))
            _datos.Add(dato);
    }

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

    private String ListToStr(IList<String> list){
        String aux = "";
        foreach (String str in list)
        {
            aux += str;
        }
        return aux;
    }
}

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'a'),
(1, N'c'),
(1, N'c'),
(1, N'd'),
(1, N'd'),
(2, N'd'),
(3, N'd'),
(3, N'e'),
(3, N'f')

--For each class, no letter will be repeated
select dbo.ConcatDistinct(name + ' '),class
from test
group by class

drop table test

Saw this idea to concatenate string in an agregate function
http://stackoverflow.com/questions/5031204/does-t-sql-have-an-aggregate-function-to-concatenate-strings#answer-5031263
but I needed it to be distinct values.

related guide:
http://www.tryexcept.com/articles/2009/09/22/using-net-assemblies-inside-sql-server-quick-start-guide.html