urgent - sql CLR Aggregate function - access grouping value

Question

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

Answer 1

Not 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