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
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