SQL Server 2005’s new PIVOT operator simplifies the process of rotating data from rows to columns for flexible analysis. However, as I described last month,
PIVOT has some limitations (see “Enhancing PIVOT for Crosstab Queries,” InstantDoc
ID 93907). Not only are the grouping columns implicit, increasing the potential for writing
code that produces undesired results, but the PIVOT IN list (which contains rotation
elements) isn’t dynamic. In addition, grouping, rotation, and
aggregation columns must be base columns in the table you
provide as input, and you can’t rotate more than one column
or specify more than one aggregation.
Last month, I showed you how to circumvent most of these limitations by providing
a table expression (a derived table or common table expression—CTE) to the PIVOT
operator as input and “preparing” columns that result from expressions. I also explained
how to use dynamic SQL to make the PIVOT column dynamic.
This month, I show you how to encapsulate all this workaround logic into a stored
procedure to provide a flexible and more complete solution for dynamic pivoting needs.
Because this solution uses dynamic SQL, which can expose your system to security risks, I
also share tips for helping you safely use the procedure. The sp_pivot and usp_pivot stored
procedures that I describe are from Inside Microsoft SQL Server 2005: T-SQL Programming
(Microsoft Press, 2006), which I wrote with Dejan Sarka and Roger Wolter.
Powerful Pivoting Logic
The sp_pivot stored procedure delivers powerful, dynamic pivoting logic. Run the code
in Listing 1, to create sp_pivot in the master database. As I noted, the stored
procedure uses dynamic SQL, which opens the door to serious SQL injection risks. (If
you aren’t familiar with SQL injection, I urge you to read the “SQL Injection” section
in SQL Server 2005 Books Online—BOL.) After I explain sp_pivot’s pivoting logic, I’ll
cover measures you can take to mitigate these security risks.
Before I dig into sp_pivot’s interface and implementation, I want to discuss the stored
procedure’s name and the fact that I created the stored procedure in the master database.
A stored procedure whose name you prefix with the characters sp_ and that you create in
the master database has special behavior. You can invoke the procedure while connected
to any database without having to database-qualify the procedure name:
USE Northwind;
EXEC dbo.sp_pivot <arguments>;
Thus, the execution context of dynamic SQL isn’t that of the master database but
rather the context of your connection (Northwind, in this case). Furthermore, you can be connected to any database and force
the context of the procedure’s execution
(in terms of dynamic SQL) to be whatever database you qualify the procedure
name with. For example, the following
statement forces the execution context of
dynamic SQL to that of the Northwind
database, regardless of which database
you’re connected to:
EXEC Northwind.dbo.sp_pivot
<arguments>;
Note that Microsoft doesn’t recommend creating stored procedures in the
master database using the sp_ prefix and
doesn’t guarantee any support if you
decide to do so. To adhere to Microsoft’s
recommendations, you might prefer to
create the stored procedure in all user
databases in which you might need it
and use a different prefix in its name. This
approach, of course, would require you
to maintain multiple copies of the stored
procedure. Now, let’s see how I implemented sp_pivot.
5 Parameters
The sp_pivot stored procedure accepts
five input parameters: @query, @on_rows,
@on_cols, @agg_func, and @agg_col.
The @query parameter represents the
input table to the PIVOT operator. The
parameter can be a table or view name or
even a query that produces a table result.
The stored procedure’s code determines
whether @query contains a valid table
or view name and, if so, constructs a
SELECT query against the object:
SET @query = N’SELECT * FROM ‘
+ @query;
If @query doesn’t contain a valid table or
view name, the stored procedure assumes
it contains a query to begin with.
The @on_rows parameter represents
the grouping columns. The stored procedure requires you to specify the grouping
columns explicitly, unlike the PIVOT
operator. You can specify one or more
columns separated by commas, or you can
specify expressions such as
@on_rows = N’EmployeeID,
MONTH(OrderDate)
AS OrderMonth’
The @on_cols parameter represents the
rotation column. Here, you can specify a
column name or expression; you don’t specify
the actual rotation elements. The stored procedure uses dynamic SQL to determine the rotation elements. If you want to rotate order years,
for example, you can specify the expression
@on_cols = N’YEAR(OrderDate)’
If you want to rotate multiple columns, you
can provide an expression that concatenates
elements. So, if you want to rotate shippers and
order years, you can specify the expression
@on_cols = N’CAST(ShipVia
AS VARCHAR(10)) + ‘’_’’ +
CAST(YEAR(OrderDate)
AS VARCHAR(10))’
The @agg_func parameter represents the
aggregate function you want to use (e.g.,
SUM, MIN, MAX, COUNT):
@agg_func = N’SUM’
Prev. page
[1]
2
3
next page 
