SQL SERVER 2005 allows custom aggregate functions-string grouping connections in tables

Source: Internet
Author: User

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-string grouping connections in tables

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.