Skip to content

Instantly share code, notes, and snippets.

@lbehm
Forked from FilipDeVos/Aggregate_Concatenate.sql
Last active May 16, 2023 14:16
Show Gist options
  • Save lbehm/563db9921e96a84b878ba01152e74363 to your computer and use it in GitHub Desktop.
Save lbehm/563db9921e96a84b878ba01152e74363 to your computer and use it in GitHub Desktop.
SQL Server Aggregate to concatenate strings - shim for STRING_AGG
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
}
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
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment