Evaluating the Pivot Solution
One benefit of the pivot-based solution is that it's generic.You can treat it as a template and use similar code when you need to implement other custom aggregates. Second, you can apply the aggregation to the elements in a desired order (by creating row numbers in a certain order).Third, the solution is pretty fast: It doesn't involve a lot of I/O, and it utilizes a set-based approach. In SQL Server 2005, it's faster because the data is scanned only once. The ROW_NUMBER function is efficient and relies on one scan of the data to calculate the row numbers. A PIVOT query is a lot like a GROUP BY query behind the scenes; you're looking at a query whose costs are very similar to that of a regular GROUP BY query that scans the data only once. In SQL Server 2000, the solution is a bit more expensive, primarily because of the row-numbers calculation. However, remember that this solution is relevant to small groups only, and if you have a good index in place, row-numbers calculation will be quick. In our case, you'd want an index on CustomerID, OrderID.
The main disadvantage of this solution is that it supports only a limited number of elements in a group. It's not practical to have a very long list of elements in terms of the length of the query string—especially in SQL Server 2000, in which you have a lengthy MAX(CASE...) expression for each item. In SQL Server 2005, the query string is shorter, so you can support more elements per group. But it still isn't realistic to support more than a few dozen items per group. In the next column in this series, I'll continue exploring other classes of solutions to custom aggregates. In the meantime, check out this month's logical puzzle and test your troubleshooting skills in "Catch That Bug!"