SQL SERVER 2005 allows custom aggregate functions

Not much to say, the explanation is followed by the complete code, which is used to spell the values of the rows of the string fields into a large string, which is what is commonly said Concat

such as the following table Dict

Id	NAME	CATEGORY
1	RED	COLOR
2	BLUE	COLOR
3	APPLE	FRUIT
4	ORANGE	FRUIT

Execute SQL statement: Select Category,dbo.concatenate (name) as names from Dict Group by category.

Get the result table as follows

Category	Names
COLOR	Redblue
FRUIT	Appleorange

If you feel you need to separate the comma or semicolon or any other delimiter you want, you can modify the following code to implement it.

In VS2005, create a SQL Server PROJECT that connects to the target library, then add an "aggregation", copy the following code, compile, deploy, and see the function in the "programmability" function "aggregate function" in SQL Server.

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
    MaxByteSize = 8000)//maximum size in bytes of persisted value
]
public CLA SS Concatenate:ibinaryserialize
{
   //<summary>
   //The Variable that holds the intermediate result of the concatenation
   ///</summary>
 & nbsp;  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);
}

<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 result S 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);
       }

return new SqlString (output);
}

public void Read (BinaryReader R)
{
Intermediateresult = new StringBuilder (r.readstring ());
}

public void Write (BinaryWriter W)
{
W.write (This.intermediateResult.ToString ());
}
}

Here are some of the more important methods: Terminate, which is the method that aggregates the last call, which returns the last value. can be any scalar of SQL Server, accumulate, which is called once per row of data and is passed to the method for the data to be processed. You can perform processing such as comparisons, merges, and so on inside a function.

SQL SERVER 2005 allows custom aggregate functions