Wednesday, May 16, 2007

User Defined Aggregate Function in SQL for String Concatenation using CLR

If you tried to concatenate strings from group in SQL, probably you would start looking for an aggregate function similar to SUM or COUNT. There is no function like this in SQL Server 2005 and even further there is no way how to create a custom aggregate function using SQL. You can use just built-in aggregates.

Fortunately, SQL Server 2005 integrates common language runtime (CLR). So, the solution is to write an aggregate function in C# and include it among built-in functions in SQL Server.

Let us show an example with aggregate function for strings concatenation. At first you should create a new project in VS - use template Class Library, name it for example MyCLRLib.

The key methods are:

  • Init() - initializes private variables
  • Accumulate() - appends the next value
  • Merge() - merges partial aggregates
  • Terminates() - returns a result aggregation
Here is completed a copy-paste code:

using System;

using System.Text;

using System.IO;

using Microsoft.SqlServer.Server;

using System.Data.SqlTypes;

 

[Serializable]

[SqlUserDefinedAggregate(

    Format.UserDefined,        //use custom 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

  // Intermediate result data of concatenation

  private StringBuilder intermediateResult;

 

  public void Init()

  {

    this.intermediateResult = new StringBuilder();

  }

 

  // If the next value is not null, append it to the end of string

  public void Accumulate(SqlString value)

  {

    if (value.IsNull)

    {

      return;

    }

    this.intermediateResult.Append(value.Value).Append(", ");

  }

 

  //Merges the partial aggregate with this aggregate

  public void Merge(Concatenate part)

  {

    this.intermediateResult.Append(part.intermediateResult);

  }

 

  //Returns the result of the aggregation when finished

  public SqlString Terminate()

  {

    string result = string.Empty;   

    if (this.intermediateResult != null

        && this.intermediateResult.Length > 0)

    {

      result = this.intermediateResult.ToString(0, this.intermediateResult.Length - 1);

    }

 

    return new SqlString(result);

  }

 

  public void Read(BinaryReader reader)

  {

    intermediateResult = new StringBuilder(reader.ReadString());

  }

 

  public void Write(BinaryWriter writer)

  {

    writer.Write(this.intermediateResult.ToString());

  }

}


Now you can register this function in SQL Server. First of all, enable .NET CLR code execution in your SQL Server:

EXEC sp_configure 'clr enabled', 1;

RECONFIGURE WITH OVERRIDE;

GO


and then create new aggregate function from your compiled assembly:

CREATE ASSEMBLY MyCLRLib FROM 'D:\MyCLRLib.dll'

GO

CREATE AGGREGATE Concatenate (@input nvarchar(1000)) RETURNS nvarchar(max)

  EXTERNAL NAME MyCLRLib.Concatenate

GO


Now you can write:

SELECT Surname, dbo.Concatenate(FirstName)

FROM Families

GROUP BY Surname



kick it on DotNetKicks.com

67 comments:

Martin said...
This comment has been removed by the author.
Martin said...

Great article, thanks!
Just one thing..
in

CREATE AGGREGATE Concatenate (@input nvarchar(1000)) RETURNS nvarchar(max)
EXTERNAL NAME MyAgg.Concatenate
GO

shouldn't there be
EXTERNAL NAME MyCLRLib.Concatenate
instead of
EXTERNAL NAME MyAgg.Concatenate ?

angwin said...

Thanks for your suggestion, Martin :)

You are right, MyCLRLib.Concatenate is thy only correct possibility.

My original class Concatenate was in MyAgg namespace, so the external name was MyCLRLib.MyAgg.Concatenate.

Now it should be correct.

Anonymous said...

Fine work. Appreciate the time you took to put this explanation together. I'll likely use this template in a variety of future applications.

BTW, everything worked the first time through for me. I was hung up on the namespace at first. SQL Server kept erroring that it couldn't find the class, but when I nixed the namespace in the C# code, voila! It worked. As stated above in the comments, I guess you must put the namespace.class in the SQL create query, if you use a namespace in the C# code (default upon creation in C# Express).

L8r,
Jage

Anonymous said...

There was a mistake

Anonymous said...

Hi,

I mostly visits this website[url=http://www.weightrapidloss.com/lose-10-pounds-in-2-weeks-quick-weight-loss-tips].[/url]Plenty of useful information on dotnet-enthusiast.blogspot.com. Do you pay attention towards your health?. Let me show you one truth. Research points that almost 60% of all USA grownups are either obese or overweight[url=http://www.weightrapidloss.com/lose-10-pounds-in-2-weeks-quick-weight-loss-tips].[/url] So if you're one of these citizens, you're not alone. Infact many among us need to lose 10 to 20 lbs once in a while to get sexy and perfect six pack abs. Now the question is how you are planning to have quick weight loss? You can easily lose with with little effort. If you improve some of your daily diet habbits then, its like piece of cake to quickly lose weight.

About me: I am blogger of [url=http://www.weightrapidloss.com/lose-10-pounds-in-2-weeks-quick-weight-loss-tips]Quick weight loss tips[/url]. I am also health expert who can help you lose weight quickly. If you do not want to go under hard training program than you may also try [url=http://www.weightrapidloss.com/acai-berry-for-quick-weight-loss]Acai Berry[/url] or [url=http://www.weightrapidloss.com/colon-cleanse-for-weight-loss]Colon Cleansing[/url] for effortless weight loss.

Anonymous said...

Hi,

Do you guys watch movies in theater or on internet? I use to rent DVD movies from [b]Blockbuster.com[/b]. Recently I discovered that we can watch all new movies on internet on day, they are released. So why should I spend money on renting movies??? So, can you guys please tell me where I can [url=http://www.watchhotmoviesfree.com]watch latest movie The Darkest Hour 2010[/url] for free?? I have searched [url=http://www.watchhotmoviesfree.com]Youtube.com[/url], [url=http://www.watchhotmoviesfree.com]Dailymotion.com[/url], [url=http://www.watchhotmoviesfree.com]Megavideo.com[/url] but, Could not find a good working link. If you know any working link please share it with me.


Thanks

buy viagra said...

hi, I like this information, I really enjoyed this post!
viagra online
generic viagra

Anonymous said...

From there, make sure that when you wager with an online casino that you know exactly what you are doing. The biggest mistake that you could possibly make would be to place bets on games that you are unsure of. For example, if you want to play blackjack at an online casino but do not know how, make sure you get some details first. If you do not, you are doing the equivalent of throwing your money out the window. The bottom line is that you can never win at an online casino if you do not know the rules of the games that you are going to play.
[url=http://www.casinolesoleil.com]casino[/url] http://www.casinolesoleil.com casino At download-based, you download software which connects to the casino, and play games through the software. Often the software can be downloaded in the back round while you enter your player information.
[url=http://www.redlotuscasino.com]online casino[/url] http://www.redlotuscasino.com casino Betting is still very much present, even when playing online casinos. You will be required to place your money on an account, and this will be transferred to the winner via PayPal or Neteller. So before handing in cash, check out the website, first! If they have more members and a good reputation, you can almost be assured that you get your moneys worth. Make sure that before downloading any of software an online casino is offering, there has to be test run of the product first. You have to be able to play for free before you download the software, since this may help in protecting you from fraud in the end. An example of this software is the Cryptologic, is safe and can be trusted, since it is listed in the Nasdaq. [url=http://www.triumphcasino.com]casino[/url] http://www.triumphcasino.com casino http://www.affrewards.com [url=http://www.affrewards.com]casino affiliate program[/url] As with all gambling bingo is totally addictive and you should be careful of its grip on you! [url=http://www.bingokisses.com]bingo[/url] [url=http://www.bingoeuphoria.com]bingo[/url] bingo bingo http://www.bingokisses.com http://www.bingoeuphoria.com Bingo is usually played in special halls where many players can play at the same time. The actual fun is the tension building up seeing other players closer and closer to the finish line. However, with the advent of the Internet, BINGO has gone online as well. There are many ways to play on the net, though the basics remain the same. You will have to complete a given pattern before anyone else in order to win. Over the net, you need not shout the computer will flash it out for you. Some online games also have chat attached to the games where you can speak with other players online and have fun right from your home, office or any other place where you can get on the net. Chatting is a great way to meet new people and pick up the tips and tricks.
http://www.casinolesoleil.com http://www.redlotuscasino.com http://www.triumphcasino.com http://www.bingokisses.com http://www.bingoeuphoria.com http://www.pulsebet.com http://www.affrewards.com http://www.thehighrollerclub.com
http://www.buyphentermine1.com [url=http://www.buyphentermine1.com]buy phentermine[/url] http://www.norxbuy.com buy phentermine

Anonymous said...

[url=http://www.concreteresurfacing.me/]Concrete Resurfacing[/url]
Initial, you'll will need to prepare the surface area. It might need to be clean without any cracks, dusting or scaling. Cleaning the area with h2o and a light scrub will reveal any injury you could possibly have. Power wash the surface and then use a warm water and bleach alternative for just about any mildew or other stains which you find.Depending around the area damage, you may need a concrete floor pro or maybe you can resolve the destruction yourself with crack and scaling fix strategies. That's as well significantly for me to go into here. There are lots of resources which you can discover for concrete floor area repair.A great deal of people think that there's no solution to outdated cracked or discolored concrete floor but to remove and replace it in case you want to enhance the appearance of one's concrete floor area. On the other hand, you'll find several alternatives to makeover an old dull concrete floor driveway, walkway, patio or any other defined surface without having having to tear out aging definite and set up a brand new 1. May be the Surface Appropriate for[url=http://www.concreteresurfacing.me/]Concrete Resurfacing[/url]?

Prior to anything at all else, nevertheless, you need to determine whether the concrete can truly be resurfaced. There are no complicated steps right here. All you will need to accomplish is inspect the defined surface area for making certain it is in sound problem. If you will find widespread, big, or deep cracks, heaving or destruction from freeze/thaw problems or [url=http://www.concreteresurfacing.me/]Concrete Resurfacing[/url] if the soil has begun to settle, then it would not be feasible to resurface the definite. If you're not certain what to try and do, ask a cement expert to check out the cement.
Right here are the different decorative concrete floor resurfacing options:
[url=http://www.concreteresurfacing.me/]Concrete Resurfacing[/url]


Concrete Resurfacing
Concrete Resurfacing
Concrete Resurfacing

Anonymous said...

xanax cost xanax and alcohol detox - xanax withdrawal vs klonopin withdrawal

Anonymous said...

who is jason lee dating http://loveepicentre.com/testimonials.php looking for single dating

Anonymous said...

viagra online without prescription can i buy generic viagra - viagra cialis online pharmacy

Anonymous said...

[url=http://loveepicentre.com/testimonials.php][img]http://loveepicentre.com/uploades/photos/5.jpg[/img][/url]
christian dating richmond virginia [url=http://loveepicentre.com/taketour.php]free mature age dating[/url] dating scammer elena sidorova
pensacola dating [url=http://loveepicentre.com/articles.php]dating australian women[/url] swing dating washington 98284
free online dating muslim in sg [url=http://loveepicentre.com/faq.php]dating in livermore[/url] relative dating atmossphere

Anonymous said...

generic viagra viagra online us no prescription - cheap viagra alternatives us

Anonymous said...

[url=http://loveepicentre.com/][img]http://loveepicentre.com/uploades/photos/5.jpg[/img][/url]
dating sister's ex [url=http://loveepicentre.com/taketour.php]kazakhstan dating[/url] dating yamaha drums
koi dating kasih [url=http://loveepicentre.com/faq.php]goth online dating services[/url] funny lines form the dating game
in relationship dating ashley website [url=http://loveepicentre.com/advice.php]sugar baby dating website[/url] dating and timing consent forms

Anonymous said...

buy soma online soma drug schedule - soma bras return policy

Anonymous said...

buy soma online no prescription buy soma 350mg - soma drug abuse signs

Anonymous said...

buy soma soma online no prescription - order soma online usa

Anonymous said...

buy somas generic for sumatriptan - sells soma bras

Anonymous said...

buy soma soma order book - buy soma drink

Anonymous said...

soma 350 mg soma drug action - soma 350 mg to get high

Anonymous said...

discount soma where to buy soma online overnight - cheap restaurants soma

Anonymous said...

buy soma online soma intimates - generic name soma muscle relaxer

Anonymous said...

buy tramadol online tramadol buy online no prescription - tramadol hcl injection

Anonymous said...

tipler physics 5th ebook http://audiobooksplanet.co.uk/Psychology-and-Law-Truthfulness-Accuracy-and-Credibility/p120646/ ebook master resell [url=http://audiobooksplanet.co.uk/Stephen-S-Roach/m119643/]make a free ebook[/url] painter ebook corel

Anonymous said...

buy alprazolam online buy xanax online cheap - xanax bars in your system

Anonymous said...

xanax online xanax online pakistan - generic xanax what do they look like

Anonymous said...

xanax online street cost 1mg xanax - xanax overdose treatment

Anonymous said...

generic xanax generic xanax xr 1mg - buy xanax hq drugs

Anonymous said...

buy tramadol buy tramadol online free shipping - ultram tramadol withdrawal symptoms

Anonymous said...

generic xanax xanax side effects list - what is xanax high like

Anonymous said...

tramadol online buy tramadol online cod only - buy tramadol online usa cheap

Anonymous said...

buy carisoprodol carisoprodol how much to overdose - carisoprodol 103

Anonymous said...

I'm curious to find out what blog system you're utilizing?
I'm experiencing some minor security problems with my latest website and I'd like to find something more safe.
Do you have any solutions?

My page ... domain names for sale
my page > domains for sale

Anonymous said...

buy carisoprodol 350 mg of carisoprodol - carisoprodol with alcohol

Anonymous said...

buy tramadol order tramadol health solutions network - tramadol for dogs with arthritis

Anonymous said...

buy carisoprodol online carisoprodol e 724 - soma carisoprodol high

Anonymous said...

buy cheap carisoprodol carisoprodol 350 side effects - carisoprodol dosage instructions

Anonymous said...

buy cialis online cialis online miglior sito - order cialis with prescription

Anonymous said...

cialis online cialis online madrid - cialis online best place buy

Anonymous said...

cialis online cialis daily dosage effectiveness - cialis usage

Anonymous said...

buy cialis online order cialis overnight delivery - generic cialis 60 mg

Anonymous said...

cialis online can you order cialis online for usa - buy cialis online usa

Anonymous said...

buy klonopin online klonopin side effects of - klonopin overdose how many mg

Anonymous said...

http://landvoicelearning.com/#57594 buy tramadol online without rx - buy tramadol 24x7

Anonymous said...

http://buytramadolonlinecool.com/#51726 medicamento tramadol 100mg - tramadol 50 mg compared to percocet

Anonymous said...

http://buytramadolonlinecool.com/#96430 tramadol joint pain - tramadol itching

Anonymous said...

buy tramadol with mastercard tramadol hcl user reviews - tramadol 100mg modified release

Anonymous said...

http://www.msn.com/ - [url=http://www.msn.com/]msn[/url] msn
msn

Anonymous said...

It appears as though the net is where it's at. I studied Computer Information and Film/Video at school, but I'm not too shabby
having a keyboard with regards to writing interesting stuff.
Does anyone know of anyone (aside from Drudge and Arianna Huffington) who has been able
to make a few bucks by blogging on a site from home?

.

Feel free to visit my homepage - vaginal mesh lawsuit

Anonymous said...

A huge dick in my pussy,a warm wet tounge up our arse and cum and pussy juice all over me.
Fuck, ozzy

Feel free to visit my web-site; hcg injections

Anonymous said...

carisoprodol 350 mg carisoprodol blue light - muscle relaxers soma carisoprodol

Anonymous said...

http://ashishmoon.com/demo/1/smf/index.php?action=profile;u=6199
http://thegamersfederation.com/www2/index.php?action=profile;u=438
http://skadoogle.com/forum/index.php?action=profile;u=45561
http://electrakittydesigns.com/test/smf/index.php?action=profile;u=1350
http://ricklyon.com/forums/index.php?action=profile;u=17131
http://www.rtifed.com/forum/User-dzsqkznv
http://www.year69.com/forums/member.php?action=profile&uid=167441
http://iusers.net/ps3/forums/member.php?2997-wsquksisyaqndw
http://onsestpasdejavu.free.fr/forum/profile.php?id=49712
http://jenniferarman.com/hiswipforums/index.php?topic=430023.new#new

Anonymous said...

Hi! I've been following your weblog for a long time now and finally got the courage to go ahead and give you a shout out from Atascocita Tx! Just wanted to tell you keep up the excellent work!

my web site :: cost of dental Implants

Anonymous said...

Good post. I learn something totally new and challenging on blogs I stumbleupon everyday.

It's always exciting to read articles from other writers and use something from their websites.

Stop by my site - dental-Implantscost.Net

Anonymous said...

I really like what you guys are up too. This kind
of clever work and coverage! Keep up the very good works guys I've added you guys to my personal blogroll.

Here is my web blog usuarios.multimania.Es

Anonymous said...

Wаy coοl! Ѕome very vаlid ροintѕ!
I aрρгeсiate you ρеnning this wrіte-up аnd thе rest of the websitе is ѵerу gooԁ.


my weblog: hcg tablets
my page - hcg diet meal plan

Anonymous said...

internet dating brisbane http://loveepicentre.com/articles/ teenage boys and perception on dating
booty adult dating sites [url=http://loveepicentre.com/contact/]interracial sex dating site[/url] dating heddon lures by hardware
dating a hooker [url=http://loveepicentre.com/advice/]dating presidente[/url] dating free millionaire online site [url=http://loveepicentre.com/user/hobbet255/]hobbet255[/url] mckeesport dating

Anonymous said...

differnt types of dating http://loveepicentre.com/ dupree dating
facebook social implications not dating [url=http://loveepicentre.com/testimonials/]musical duets adult dating[/url] adult dating bartenders bartering services
online single dating sites [url=http://loveepicentre.com/contact/]100 free dating sites in canada[/url] dreammates dating internet service [url=http://loveepicentre.com/user/shinnyheart/]shinnyheart[/url] dating servisces

Anonymous said...

online sex dating free http://loveepicentre.com/taketour/ who i shane wet currently dating
christian singles dating services [url=http://loveepicentre.com/faq/]jag dating[/url] digimon adventure dating sim
fur coat dating [url=http://loveepicentre.com/map/]free dating rochesteer new york[/url] romance romances in china lover dating [url=http://loveepicentre.com/user/kofe_1/]kofe_1[/url] dating customs and finland

Anonymous said...

kiss a frog dating site http://loveepicentre.com/success_stories/ history of computer dating
fetish dating sydney [url=http://loveepicentre.com/contact/]who is nicole richie dating[/url] dating 4 fun
happy pancake dating [url=http://loveepicentre.com]devendra dating 2009[/url] adventist programs on dating [url=http://loveepicentre.com/user/janny21/]janny21[/url] who is kate hudson dating

Anonymous said...

student professor dating statistics http://loveepicentre.com/articles/ dating younger men
is rhianna dating chris brown [url=http://loveepicentre.com/success_stories/]asian dating boston mass[/url] free ionline dating service
is interracial dating wrong in christianity [url=http://loveepicentre.com/]dating for credit card users[/url] dating in uganda [url=http://loveepicentre.com/user/ericyryus/]ericyryus[/url] dating malaysian

Anonymous said...

Blogger: .NET Enthusiast - Post a Comment [url=http://www.cymbaltarxbuyonline.net/] Generic For Cymbalta [/url] cymbalta price - Low Cost Cymbalta - cymbalta cost [url=http://www.buyingtopamaxcheap.net/]topiramate weight loss [/url] topamax no prescription - topamax cost - buy topiramate no prescription - http://www.buyingtopamaxcheap.net/ [url=http://www.cymbaltarxbuyonline.net/]cheap cymbalta [/url] generic cymbalta - buy cymbalta online - buy cymbalta online no prescription - http://www.cymbaltarxbuyonline.net/ [url=http://www.buyingtopamaxcheap.net/] Buy Topiramate [/url] topamax for sale - Cost Of Topamax - topamax price

Anonymous said...

Shemale dick
http://asianladyboy.erolove.in/?nataly
shaemle video shemals shemeal sex movies freeshemale movie semales.com

Anonymous said...

Contemporary launched porn site
http://shemaledating.sexblog.pw/?sydnie
erotic sport erotic nights erotic music erotic board game erotic phrases

Anonymous said...

where to buy ritalin buy ritalin forum - is it possible to buy ritalin online