String concatenation aggregate function with assembly

By
Mateusz
 – January 28, 2011Posted in: Microsoft SQL Server

Problems

In this post I will discuss the aggregate function that concatenates strings. The article shows how to solve the problem and focuses on assembly function.

The example is shown on AdventureWorks 2008 database samples. The given problem is to assign products from table Production.Product to colors and show them in string separated by commas.

Input table:

SELECT color, name
FROM AdventureWorks.Production.Product
    Color           Name
   ----------- ----------------------------------------
    Black           Chanling
    Black           Full Finger Gloves
    ...
    Blue           Classic Vest
    Blue           M Classic
    ...
    ...
    Grey           Touring Partners

    (xx row(s) affected)

Expected result:

   Color          Product List
    ----------- --------------------------------------------------------------
    Black        Chanling, Full Finger Gloves  , ...
    Blue         Classic Vest, M Classic, ...
    Grey         Touring Partners, ...
    ...

    (xx row(s) affected)

XML PATH Method:

SELECT p1.color,
( SELECT name + ','
FROM AdventureWorks.Production.Product p2
WHERE p2.color = p1.color
ORDER BY name
FOR XML PATH('') ) AS Products
FROM AdventureWorks.Production.Product p1

GROUP BY p1.Color ;

Aggregation with assembly function – Common Language Runtime

Visual Studio dll project must be created. In SQL Server 2008 R2  required .NET version is 3.5 or lower.

CLR code:

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

[Serializable]
[SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize = 8000)]
public struct strconcat : IBinarySerialize
{
private List values;

public void Init()
{
this.values = new List();
}
public void Accumulate(SqlString value)
{
this.values.Add(value.Value);
}
public void Merge(strconcat value)
{
this.values.AddRange(value.values.ToArray());
}

public SqlString Terminate()

{
return new SqlString(string.Join(", ", this.values.ToArray()));
}

public void Read(BinaryReader r)
{
int itemCount = r.ReadInt32();
this.values = new List(itemCount);
for (int i = 0; i <= itemCount - 1; i++)
{
this.values.Add(r.ReadString());
}
}

public void Write(BinaryWriter w)
{
w.Write(this.values.Count);
foreach (string s in this.values)
{
w.Write(s);
}
}
}

Compiled library must be imported to destination database. It can be done in Management Studio.

Next step is to create SQL aggregate function.

CREATE AGGREGATE String_aggregate(@INPUT nvarchar(8000))
RETURNS nvarchar(8000)
EXTERNAL NAME [aggregate].[strconcat];
GO

After function is created, it can be used like build-in functions ie. with GROUP BY. The result set was limited because max varchar size is 8000.

 SELECT color,
dbo.String_aggregate(Name),
FROM AdventureWorks.Production.Product
WHERE listprice > 100
GROUP BY color
Tags: , , ,

About the Author