Take Advantage of New T-SQL Features (Continued)

Implement Pivots in T-SQL
Developers upsizing from Jet to SQL Server databases have complained long and loud about T-SQL's lack of PIVOT and TRANSFORM keywords for writing crosstab queries. SQL Server 2005 finally implements PIVOT, but not TRANSFORM. PIVOT requires a FOR operator and an IN predicate that contains the equivalent of Jet's fixed column headings. The FOR operator doesn't support an expression as the PIVOT argument. Here's the generic PIVOT operator syntax:

SELECT RowHeader1, RowHeader2, ... 
   ColValue1 AS ColHeader1, 
   ColValue2 AS ColHeader2, 
   ColValue3 AS ColHeader3, ...
FROM TableName
PIVOT (Aggregate(ValueColName) 
   FOR ValueSourceColName 
   IN(ColValue1, ColValue2, ColValue3, ...))

The OrdersByProduct1997.sql sample T-SQL script creates a rollup table from the Northwind database's online transaction processing (OLTP) records. The OrdersByProduct1997 source table has these columns: CategoryName (nvarchar(20)), ProductName (nvarchar(40)), Quarter (int), and ProductOrders (money) (see Figure 1). A PIVOT expression (OrdersByProduct1997Pivot.sql) returns a crosstab rowset with columns that report quarterly and annual sales for 1997 (see Figure 2):

IF OBJECT_ID (N'OrdersByProduct1997Pivot', N'U') 
   IS NOT NULL 
   DROP TABLE dbo.OrdersByProduct1997Pivot 
GO
CREATE TABLE dbo.OrdersByProduct1997Pivot 
   (Category nvarchar(20), Product nvarchar(40), 
   Y1997Q1 money, Y1997Q2 money, Y1997Q3 
   money, Y1997Q4 money, Y1997Totals money)
GO

INSERT dbo.OrdersByProduct1997Pivot(Category, 
   Product, Y1997Q1, Y1997Q2, Y1997Q3, Y1997Q4) 
   SELECT CategoryName AS Category, 
      ProductName AS Product, [1] AS Y1997Q1, 
      [2] AS Y1997Q2, [3] AS Y1997Q3, [4] AS Y1997Q4
   FROM dbo.OrdersByProduct1997
   PIVOT (SUM(ProductOrders) FOR 
      Quarter IN([1], [2], [3], [4])) AS QuarterlyOrders 
   ORDER BY CategoryName, ProductName
GO

UPDATE dbo.OrdersByProduct1997Pivot 
SET Y1997Totals = ISNULL(Y1997Q1, 0) + 
   ISNULL(Y1997Q2, 0) + ISNULL(Y1997Q3, 0) + 
   ISNULL(Y1997Q4, 0)
GO
SELECT * FROM dbo.OrdersByProduct1997Pivot

This PIVOT expression (in bold) creates and inserts rows in an OrdersByProduct1997Pivot table. The UPDATE statement computes row totals by substituting 0 for NULL ProductOrders values. Updating all NULL values to 0 lets you cross-foot the resultset by inserting a row with the SUMs of the five numeric columns.

The persistent table lets you test UNPIVOT operator syntax (OrdersByProduct1997Unpivot.sql) to return a rowset that's identical to the OrdersByProduct1997 source table:

SELECT Product AS ProductName, Category 
   AS CategoryName, Quarter, ProductOrders 
FROM (SELECT Category, Product, Y1997Q1 AS [1], 
   Y1997Q2 AS [2],   Y1997Q3 AS [3], Y1997Q4 AS [4] 
   FROM dbo.OrdersByProduct1997Pivot) AS P1
   UNPIVOT (ProductOrders 
      FOR Quarter IN([1], [2], [3], [4])) 
      AS QuarterlyOrders 
GO

Note that the UNPIVOT query's sub-SELECT statement that forms the first FROM clause is the PIVOT query's SELECT clause with the crosstab column names and aliases interchanged. The UNPIVOT clause is the PIVOT clause with the SUM aggregate function removed.

Use Ranking Functions and Windowing Clauses
The T-SQL TOP (n) [PERCENT] operator introduced DBAs and developers to rowsets based on the ranking of the rows that's determined by the sort order that you specify with an ORDER BY clause. SQL Server required n to be a literal integer or—with the PERCENT modifier—a float value. SQL Server 2005 expands on this by letting you substitute a variable of the bigint or float data type for the literal numerical value:

DECLARE @numRows AS bigint;
SET @numRows = 10;
SELECT TOP (@numRows) * FROM Products
   ORDER BY UnitPrice DESC
GO
DECLARE @pctRows AS float;
SET @pctRows = 12.5;
SELECT TOP (@pctRows) PERCENT * FROM Products
   ORDER BY UnitPrice DESC
GO