SQL Server Magazine

You can even specify a user-defined aggregate (UDA) function name.

Last, the @agg_col parameter represents the aggregation column that you’ll provide to the aggregate function as input. You can specify a column name, an expression (e.g., N'Quantity * UnitPrice'), or even N'*' if you want to count rows. Note that sp_pivot doesn’t work around PIVOT’s inability to calculate multiple aggregations; you’re still limited to one aggregation.

You’re now ready to use the sp_pivot stored procedure to provide flexible, dynamic pivoting functionality. Let’s first look at a few examples of using the procedure and then walk through its implementation. In the following example, sp_pivot returns the sum of freight for each employee and shipper, generating the results that Table 1 shows:

EXEC Northwind.dbo.sp_pivot
 @query  = N’dbo.Orders’,
 @on_rows  = N’EmployeeID’,
 @on_cols  = N’ShipVia’,
 @agg_func = N’SUM’,
 @agg_col  = N’Freight’;

Table 2 shows the results of the next example, which returns the count of orders for each employee and shipper:

EXEC Northwind.dbo.sp_pivot
  @query  = N’dbo.Orders’,
  @on_rows  = N’EmployeeID’,
  @on_cols  = N’ShipVia’,
  @agg_func = N’COUNT’,
@agg_col  = N’*’;

Table 3 shows the results of running the third sp_pivot example code, which returns the sum of value (Quantity * UnitPrice) for each month and year:

EXEC Northwind.dbo.sp_pivot
  @query  = N’SELECT
  OrderDate, Quantity, UnitPrice
FROM dbo.Orders AS O
JOIN dbo.[Order Details]
  AS OD ON O.OrderID =
  OD.OrderID’,
  @on_rows  =
  N’MONTH(OrderDate)
  AS OrderMonth’,
  @on_cols  =
  N’YEAR(OrderDate)’,
  @agg_func = N’SUM’,
  @agg_col  =
N’Quantity * UnitPrice’;

And Web Table 1 (which you can view at http://www.sqlmag.com, InstantDoc ID 94268) shows the results of the last example, which returns the sum of freight for each employee and shipper_year:

EXEC Northwind.dbo.sp_pivot
   @query  = N’dbo.Orders’,
  @on_rows  = N’EmployeeID’,
  @on_cols  =
  N’CAST(ShipVia AS
  VARCHAR(10)) + ‘’_’’ +
  CAST(YEAR(OrderDate)
  AS VARCHAR(10))’,
  @agg_func = N’SUM’,
@agg_col  = N’Freight’;

Now that you’ve seen how you can use sp_pivot to rotate data from rows to columns, let’s look at how the stored procedure encapsulates the logic of the PIVOT workaround techniques I covered last month. At callout A in Listing 1, the code determines whether the input is a valid table or view name, as I mentioned earlier. If it is, the code constructs a SELECT query against the table or view; if it isn’t, the stored procedure assumes you provided a query to begin with. The code then creates a derived table called Query out of the query string stored in @query.

At callout B, the code circumvents the limitation that * isn’t allowed as input to the COUNT function by substituting * with the constant 1. And at callout C, the procedure uses dynamic SQL to query the list of rotation elements and store the list in the @cols local variable. The code constructs a FOR XML PATH query string that queries the distinct rotation elements (expressed by @on_cols) from the derived table stored in @query, concatenates them, and returns the concatenated string by using the @result output parameter. You can use other techniques besides FOR XML PATH to concatenate strings, such as using a cursor, but those approaches require more code and are much slower.

Prev. page     1 [2] 3     next page