Skip to content

Instantly share code, notes, and snippets.

@FilipDeVos
Last active October 15, 2023 03:36
Show Gist options
  • Save FilipDeVos/5b7b4addea1812067b09 to your computer and use it in GitHub Desktop.
Save FilipDeVos/5b7b4addea1812067b09 to your computer and use it in GitHub Desktop.
SQL Server Aggregate to concatenate strings
-- 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
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());
}
}
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
Copy link

ghost commented Oct 23, 2015

kikel emelni, és lekel írni,,?

@jalgaba
Copy link

jalgaba commented Mar 19, 2016

Read your answer from:
http://stackoverflow.com/questions/5031204/does-t-sql-have-an-aggregate-function-to-concatenate-strings#answer-5031263
This function is excellent. It replaced a lot of my clunky FOR XML subqueries with better performance. This function should be integrated into sql server.
For anyone looking for a more thorough guide on how to use and modify this code, this helped me alot:
http://www.tryexcept.com/articles/2009/09/22/using-net-assemblies-inside-sql-server-quick-start-guide.html
(i.e. I didn't know how to create the CREATE ASSEMBLY script in binary form )

@sceasary
Copy link

sceasary commented Mar 27, 2018

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment