Free: custom aggregate from Microsoft

I recall from one of the MSDN events that I went to, that SQL Server 2005 has added the ability to do custom aggregates via dotnet.  Naturally, anyone would ask the obvious question: why would I want such a thing.  The example they gave was really stupid.  It was something like "calculate the product of a column".  Duh.  Who would want to do that.  This week, I actually came up with a good idea for a custom aggregate.

So, when would you want a custom aggregate?

Short answer: you would want a custom aggregate any time that you might need a cursor.
Long answer: have you ever wanted to aggregate, or roll-up a column of values, such as a string column?  For instance, if you had a database for an issue-tracking system.  It had the ability to associate multiple keywords or products or categories with the issue.  When you run a report, it would be WAY better if you showed one line for the issue and all of the keywords, rolled-up, into a single item instead of as an embedded list.  Like this:

SELECT IssueID, Title, Description, IssueDate, Status, StatusDate, Concatenate(Keyword.Name) as Keywords
FROM Issue INNER JOIN IssueKeyword ON Issue.IssueID=IssueKeyword.IssueID
          INNER JOIN Keyword ON IssueKeyword.KeywordID=Keyword.KeywordID
GROUP BY IssueID, Title, Description, IssueDate, Status, StatusDate

Result:
  IssueID   Title          Description   IssueDate   Status   StatusDate  Keywords
    122       Test Bug   Fix me         1/21/2009   New    1/21/2009     Test, Exchange Server, Accounting

That would be way better than having Keywords show up on three lines or just showing one of the keywords.

Okay.  I started writing my own custom aggregate and it wasn’t working out so good.  So, I started surfing for help and found out that Microsoft actually has a toolkit that comes with a pre-build custom aggregate that concatenates strings.  DUDE!  It was called SqlServerSamples.msi

Anyway, it took a little fiddling to get SQL Server 2005 to permit (dotnet) CLR integration stuff to run and giving permissions for execution to users but now it is in place and supremely rulez!

If you can’t find the code from Microsoft or just want to see my version of it, send me an email.  I keep that proj on my thumb drive all the time now.  I could also post the code to my server if I get enough requests.  I’m always glad to share

Advertisements

About Tim Golisch

I'm a geek. I do geeky things.
This entry was posted in Database. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s