Hey I'm trying to create a function that will allow me to concatonate a string when grouping resulting in a list of values seperated by a comma. For example, group by CategoryID and see a list of Products in each category.
i.e.
1 apples, oranges, lemons
2 mustard, relish, salt, pepper
3 wheat, white, whole grain
I'm creating a CLR aggregate function that will let me do this in a simple select function.
i.e.
SELECT CategoryId, dbo.strconcat(ProductName) AS Products
FROM Products
GROUP BY CategoryId
I am pretty new to C# and was hoping someone could help me with what seems to be a simple coding error. Below is the CLR function 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);
}
}
}
On the line "private List values;" the word List is underlined with this error:
Using the generic type 'System.Collections.Generic.List<T>' requires '1' type arguments.
Any ideas on how to get this function working?
~DB
String Concatonate function for Group By
Posted - 2009-02-25 : 16:36:23
