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