This article is the last in my four-part series about solutions to custom-aggregate requests. SQL Server's built-in aggregate functions are useful, but what if you need other types of aggregations that SQL Server doesn't provide? In the series' first three articles—"Custom Aggregations: The CursorTechnique"(http://www.sqlmag.com, March 2006, InstantDoc ID 49038),"Custom Aggregations: The Pivoting Technique" (May 2006, InstantDoc ID 49675), and "Custom Aggregations: User-Defined Aggregates" (June 2006, InstantDoc ID 49983), I discussed cursor-based, pivot-based, and UDA-based solutions, respectively. This month, I'll present a fourth solution: specialized solutions.
Specialized solutions are unique to specifically requested aggregate calculations.The goal is to achieve optimal performance for the given task by forsaking the generality of the solution. In other words, you obtain optimal performance by compromising other aspects of the solution. Specialized techniques wouldn't necessarily apply to other custom-aggregate calculations, so you won't be able to use the solution as a template for other calculations.You'll have to "invent" a different solution for each custom-aggregate calculation.
As with the rest of the columns in this series, I'm using string concatenation as my example of a customaggregation task. Here's a quick summary of the two sample concatenation tasks featured in the first three articles: The first task is to use the Northwind database to return each customer and, for each, a concatenated list of order IDs expressed as a comma-separated list of values and sorted by order ID, as Table 1 shows.The second task is to aggregate distinct, concatenated employee IDs for each customer, as Table 2 shows.
Remember that we're evaluating the usefulness of each solution based on several factors: Is the solution generic? Does the solution support an unlimited number of elements in a group, and does it have any special limitations? Can you apply the aggregation to the elements in a desired order? How well does it perform? Finally, is the solution set-based or is it iterative/procedural? Let's take a look at two specialized solutions: one based on assignment SELECT and another based on the FOR XML PATH option.
Custom Aggregates Based on Assignment SELECT
T-SQL supports a non-standard feature that lets you use a SELECT statement to query data and assign values from the source table to variables.I refer to this capability as an assignment SELECT. If the source table contains more than one row, the assignment will take place multiple times—once per row. You can achieve string concatenation by concatenating the current content of the variable with a character string stored in the current row:
SELECT @variable = @variable
+
FROM ;
You can rely on this capability to provide solutions for the string-concatenation requests I presented earlier in the article. I'll start with the request to return the concatenated order IDs for each customer. First, you create a function that accepts a customer ID as input and returns the concatenated order IDs for the given customer. Run the code in Listing 1 to create the fn_concatorders function, which accomplishes this task.
The function's code declares a variable called @ArrOrders and initializes it with an empty string. The code then invokes an assignment SELECT that—for each order belonging to the input customer (@cid)— concatenates the current content of @ArrOrders with a comma and the current OrderID. When the assignment SELECT finishes, @ArrOrders contains the concatenated order IDs for the input customer, with an extra comma at the beginning. The RETURN command returns the content of @ArrOrders after using the STUFF function to remove the first comma.To apply the function to each customer, simply query the Customers table and invoke the function in the SELECT list, providing it with the CustomerID column as input, as follows:
SELECT CustomerID,
dbo.fn_concatorders(CustomerID)
AS ArrOrders
FROM dbo.Customers;
Prev. page
[1]
2
next page 
