Is possible to use CLR aggregate with Hash Group hint?
I tried GROUP_CONCAT() aggregate function (you can find it on codeplex http://groupconcat.codeplex.com/), but the problem is with each custom aggregate function I have tried.
When I run:
SELECT
Position,
dbo.GROUP_CONCAT(Name)
FROM TestTable
GROUP BY Position
OPTION (HASH GROUP)
I always get error:
Msg 8622, Level 16, State 1, Line 1 Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.
Here is my test table and code to create assembly you can find at http://groupconcat.codeplex.com/:
CREATE TABLE [dbo].[TestTable](
[Id] [int] NOT NULL,
[Name] [varchar](50) NULL,
[Position] [int] NULL
)
GO
INSERT [dbo].[TestTable] ([Id], [Name], [Position]) VALUES (1, N'dfgdfg', 1)
GO
INSERT [dbo].[TestTable] ([Id], [Name], [Position]) VALUES (2, N'dfdfg', 2)
GO
INSERT [dbo].[TestTable] ([Id], [Name], [Position]) VALUES (3, N'rgererg', 1)
GO
Here is aggregate function definition in C#:
[Serializable]
[SqlUserDefinedAggregate(Format.UserDefined,
MaxByteSize = -1,
IsInvariantToNulls = true,
IsInvariantToDuplicates = false,
IsInvariantToOrder = true,
IsNullIfEmpty = true)]
public struct GROUP_CONCAT : IBinarySerialize
{
private Dictionary<string, int> values;
public void Init()
{
this.values = new Dictionary<string, int>();
}
public void Accumulate([SqlFacet(MaxSize = 4000)] SqlString VALUE)
{
if (!VALUE.IsNull)
{
string key = VALUE.Value;
if (this.values.ContainsKey(key))
{
this.values[key] += 1;
}
else
{
this.values.Add(key, 1);
}
}
}
public void Merge(GROUP_CONCAT Group)
{
foreach (KeyValuePair<string, int> item in Group.values)
{
string key = item.Key;
if (this.values.ContainsKey(key))
{
this.values[key] += Group.values[key];
}
else
{
this.values.Add(key, Group.values[key]);
}
}
}
[return: SqlFacet(MaxSize = -1)]
public SqlString Terminate()
{
if (this.values != null && this.values.Count > 0)
{
StringBuilder returnStringBuilder = new StringBuilder();
foreach (KeyValuePair<string, int> item in this.values)
{
for (int value = 0; value < item.Value; value++)
{
returnStringBuilder.Append(item.Key);
returnStringBuilder.Append(",");
}
}
return returnStringBuilder.Remove(returnStringBuilder.Length - 1, 1).ToString();
}
return null;
}
public void Read(BinaryReader r)
{
int itemCount = r.ReadInt32();
this.values = new Dictionary<string, int>(itemCount);
for (int i = 0; i <= itemCount - 1; i++)
{
this.values.Add(r.ReadString(), r.ReadInt32());
}
}
public void Write(BinaryWriter w)
{
w.Write(this.values.Count);
foreach (KeyValuePair<string, int> s in this.values)
{
w.Write(s.Key);
w.Write(s.Value);
}
}
}
