CLR custom aggregates for SQL Server

Having the tables "product" and "product_stock" I want to display for each product the comma-separated list of sizes and this list is ordered by a specific order.

CREATE TABLE product(
product_id int,
code varchar(50) )

CREATE TABLE product_stock(
product_stock_id int,
size_order int ,
size varchar(20) ,
product_id int )

-- desired output
Product_id Sizes In Stock
35 UK7, UK8, UK9, UK11, UK10.5, UK12, UK10
36 L, M, S, XL, XXL, XXXL


In SQL this can be achieved with cursors, but everyone suggest not to use them. After some digging I found out about custom aggregates and they saved the day.

In the MSDN article is an example which concatenates the strings and this I'm writing does the same, only it adds the ordering feature.

The problem occurs with the ordering because SQL doesn't allow to use the "ORDER BY" clause in subqueries or if the "GROUP BY" exists, then the only columns allowed are the ones in grouping or in the aggregate functions. Raises the following errors:
Msg 1033, Level 15, State 1, Line 9
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

and
Msg 8127, Level 16, State 1, Line 4
Column "product_stock.size_order" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.


It can be used "SELECT TOP 100 PERCENT" to get rid of the annoying message, but is not guaranteed that the order is preserved.

So the following code will solve the problem.

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


[Serializable]
[SqlUserDefinedAggregate(
Format.UserDefined, //custom serialization
IsInvariantToNulls = true,
IsInvariantToDuplicates = false,
IsInvariantToOrder = false, //MSDN: Reserved for future use. This property is not currently used by the query processor: order is currently not guaranteed.
MaxByteSize = 8000)
]
public class ConcatenateSizes : IBinarySerialize
{
[Serializable]
public class Size
{
public int SizeOrder { get; set; }
public string SizeName { get; set; }
}
private List sizes = null;
public void Init()
{
sizes = new List();

}
public void Accumulate(SqlString sizeName)
{
if (sizeName.IsNull)
return;

var trims = sizeName.Value.Split(',');
sizes.Add(new Size
{
SizeOrder = Int32.Parse(trims[0]),
SizeName = trims[1]
});
}
public void Merge(ConcatenateSizes part)
{
sizes.AddRange(part.sizes);
}

public SqlString Terminate()
{
sizes.Sort(new SizeComparer());
var sb = new StringBuilder();
if (sizes.Count > 0)
{
foreach (var s in sizes)
sb.Append(s.SizeName).Append(", ");
var result = sb.ToString().TrimEnd(',', ' ');
return new SqlString(result);
}
return null;
}
public void Read(BinaryReader reader)
{
sizes = new List();
try
{
while (true)
{
var size = new Size();
size.SizeName = reader.ReadString();
size.SizeOrder = reader.ReadInt32();
if ( !String.IsNullOrEmpty(size.SizeName))
sizes.Add(size);
else
{
break;
}
}

}
catch (EndOfStreamException)
{
}

}
public void Write(BinaryWriter writer)
{
foreach (var size in sizes)
{
writer.Write(size.SizeName);
writer.Write(size.SizeOrder);
}
}

public class SizeComparer : IComparer
{
#region IComparer Members

public int Compare(Size x, Size y)
{
if (x.SizeOrder != y.SizeOrder)
return x.SizeOrder.CompareTo(y.SizeOrder);
return x.SizeName.CompareTo(y.SizeName);
}

#endregion
}

}


The function is used like:


select p.code, s.sizes
from product p
inner join
(
select ps.product_id, dbo.ConcatenateSizes(convert(varchar(20), ps.size_order) + ',' + ps.size) as sizes
from product_stock ps
group by ps.product_id
) s
on p.product_id = s.product_id

Comments

Popular posts from this blog

IIS 7.5, HTTPS Bindings and ERR_CONNECTION_RESET

Table Per Hierarchy Inheritance with Column Discriminator and Associations used in Derived Entity Types

About the "The Controls collection cannot be modified because the control contains code blocks" symptom