Like A User Defined Aggregate Function For Concate

May 8, 2008

Hi,

I have a table T1 with two fields. Field1 has repeated values and Field2 has unique values for any given Field1 value. F1 can have maybe 1000 different values while F2 maybe 20.

I would like to create a T2 table where Field1 has unique values and Field2 contains the concatenation of T1.Field1 values for each Field1 value.

T1
F1F2
1a
1b
2a
2c
2d

T2
F1F2
1ab
2acd

It is like a user defined aggregate function for concatenation. Any idea?

Cornelius

View 2 Replies

CLR User Defined Aggregate Function

Oct 4, 2006

I am trying to modify the C# example given in the "Invoking CLR User-Defined Aggregate Functions" in SQL Server 2005 Books Online. I want to add a parameter that is used as the list delimeter, instead of the hard coded comma that is used. I am not a C# programmer and don't know where to begin. Please help!

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

[Serializable]
[SqlUserDefinedAggregate(
Format.UserDefined, //use clr serialization to serialize the intermediate result
IsInvariantToNulls = true, //optimizer property
IsInvariantToDuplicates = false, //optimizer property
IsInvariantToOrder = false, //optimizer property
MaxByteSize = 8000) //maximum size in bytes of persisted value
]
public class Concatenate : IBinarySerialize
{
/// <summary>
/// The variable that holds the intermediate result of the concatenation
/// </summary>
private StringBuilder intermediateResult;

/// <summary>
/// Initialize the internal data structures
/// </summary>
public void Init()
{
this.intermediateResult = new StringBuilder();
}

/// <summary>
/// Accumulate the next value, not if the value is null
/// </summary>
/// <param name="value"></param>
public void Accumulate(SqlString value)
{
if (value.IsNull)
{
return;
}

this.intermediateResult.Append(value.Value).Append(','); /// I want to change to comma to a variable
}

/// <summary>
/// Merge the partially computed aggregate with this aggregate.
/// </summary>
/// <param name="other"></param>
public void Merge(Concatenate other)
{
this.intermediateResult.Append(other.intermediateResult);
}

/// <summary>
/// Called at the end of aggregation, to return the results of the aggregation.
/// </summary>
/// <returns></returns>
public SqlString Terminate()
{
string output = string.Empty;
//delete the trailing comma, if any
if (this.intermediateResult != null
&& this.intermediateResult.Length > 0)
{
output = this.intermediateResult.ToString(0, this.intermediateResult.Length - 1);
}

return new SqlString(output);
}

public void Read(BinaryReader r)
{
intermediateResult = new StringBuilder(r.ReadString());
}

public void Write(BinaryWriter w)
{
w.Write(this.intermediateResult.ToString());
}
}

View 4 Replies View Related

Managed C++ User-defined Aggregate Function

Mar 16, 2007

Hi all,

I'm attempting to write an aggregate function in C++ to compare performance with the equivalent function in C#.

However, I'm having problems getting SQL Server to see the function in the assembly. It allows me to load the assembly into the database, but I can't see the type in it.

Here's my code:

// CPPTest.h

#pragma once

using namespace System;
using namespace Microsoft::SqlServer::Server;
using namespace System::Data::SqlTypes;
using namespace System::Data::SqlClient;

namespace CPPTest {

[Serializable]
[Microsoft::SqlServer::Server::SqlUserDefinedAggregate(
Format::Native,
Name="AGG_CPP_OR")]
public ref struct AGG_CPP_OR
{
public:
void Init();
void Accumulate(SqlInt32 Value);
void Merge(AGG_CPP_OR^ Group);
SqlInt32 Terminate();

private:
SqlInt32 m_accum;
};

}

// CPPTest.cpp

#include "stdafx.h"

#include "CPPTest.h"

void CPPTest::AGG_CPP_OR::Init()
{
m_accum = 0;
}

void CPPTest::AGG_CPP_OR::Accumulate(SqlInt32 Value)
{
m_accum = m_accum | Value;
}

void CPPTest::AGG_CPP_OR::Merge(CPPTest::AGG_CPP_OR^ Group)
{
m_accum = m_accum | Group->m_accum;
}

SqlInt32 CPPTest::AGG_CPP_OR::Terminate()
{
return m_accum;
}

Compile it with /clr:safe option and it can be loaded as an assembly into SQL Server 2005 (9.0.1399), but the AGG_CPP_OR type is not seen as an aggregate function. I've also tried implementing IBinarySerialize and setting Format to Format::UserDefined (and putting in MaxByteSize) but it makes no difference.

Does anyone know what I'm missing here?

Many thanks,