Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 .NET Inside SQL Server (2005)
 String Concatonate function for Group By

Author  Topic 

DBowlerHB
Starting Member

1 Post

Posted - 2009-02-25 : 16:36:23
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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-25 : 17:38:43
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

matju
Starting Member

3 Posts

Posted - 2011-01-28 : 07:57:32
Working code here: http://biblog.pl/?p=140
Go to Top of Page

shane.garven
Starting Member

2 Posts

Posted - 2011-03-29 : 11:52:27
What do you want/need List<> to hold? A string = List<string>. An int List<int>... In your case you probably want private List<string>

--
Cheers,
Shane
Go to Top of Page
   

- Advertisement -