urgent - sql CLR Aggregate function - access grouping value
-
Sunday, July 29, 2012 6:34 PM
I am looking to build a SQL CLR Aggregate function. In the CLR Function, I need to receive the grouping values. Is there a way to receive the grouping values in the SQL CLR Aggregation WITHOUT explicitly passing it as a parameter to the Aggregate function?
For instance, let’s assume my sample Table1 and Query is as below. In the CLR aggregate function, I want to know the grouping column names and their corresponding values in SQL CLR function, for instance, when grouping by ProductFamily, I want to know that the group by is based on “ProductFamily” column and the value for the first row is “Office365”….
ProductFamily Product Sales
Office365 Excel $300
Office365 Word $200
Office365 Office $100
Windows WinXP $200
Windows Vista $200
SELECT ProductFamily, CLR_AGGREGATE(Sales)
FROM Table1
GROUP BY ProductFamily
SELECT Product, CLR_AGGREGATE(Sales)
FROM Table1
GROUP BY Product
Thanks
All Replies
-
Sunday, July 29, 2012 9:41 PMModeratorNot that I know of. Just curious, but why do you need to know the value at aggregation time? You could always use the query that produces the aggregation as a subquery or derived table or CTE and then “post-process” the results in the main query. You could conceivably pass in a UDT (defined in the table definition as a computed column, based on the other columns) as the second value that reflects whatever you’d like (I’ve not tried this myself), but it doesn’t really seem necessary.Cheers, Bob
.png)
