Catch That Bug! - 23 May 2006

May's Bug Solution: In SQL Server 2005, you run the following PIVOT query in the Northwind database:

SELECT CustomerID, [1],[2],[3],   [4],[5],[6],[7],[8],[9] FROM dbo.Orders   PIVOT(COUNT(OrderID) FOR EmployeeID IN([1],[2],[3],   [4],[5],[6],[7],[8],[9])) AS P; 

You expect to get a single row for each customer, along with the number of orders placed by each employee for that customer. Because there are 89 customers in the Orders table that have orders, you expect to get 89 rows in the result. However, you don't get the expected result. Rather, you end up getting 830 rows. Where's the bug in the code?

In the May article, I mentioned that the PIVOT operator has an implicit grouping phase.The implicit grouping list is constructed from all attributes in the table that appears to the left of the PIVOT keyword, excluding attributes that were mentioned in the parentheses following the PIVOT keyword. In our case, the implicit grouping list became CustomerID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, Ship-PostalCode, ShipCountry. You got 830 rows back because there are 830 unique combinations of values in the aforementioned grouping list in the table.

To get the desired result, the PIVOT operator must be provided with an input table that contains only the columns that are relevant to its activity.To achieve this result, you can prepare a derived table or a CommonTable Expression (CTE),as follows:

SELECT CustomerID, [1],[2],[3],   [4],[5],[6],[7],[8],[9] FROM (SELECT CustomerID,   EmployeeID, OrderID FROM   dbo.Orders) AS D PIVOT(COUNT(OrderID) FOR EmployeeID IN([1],[2],[3],   [4],[5],[6],[7],[8],[9])) AS P; 

The derived table D contains only the relevant columns for PIVOT's activity—that is, OrderID, which is used as the input to the aggregate function; EmployeeID, which contains the elements that you want to rotate; and CustomerID, which will be used as PIVOT's implicit grouping list.

June's Bug: In SQL Server 2005, you create the followingVSortedOrders view in the Northwind database:

USE Northwind; GO CREATE VIEW dbo.VSortedOrders AS SELECT TOP(100) PERCENT   CustomerID, OrderID, OrderDate FROM dbo.Orders ORDER BY CustomerID, OrderID; GO 

You then issue the following query:

SELECT CustomerID, OrderID,   OrderDate FROM dbo.VSortedOrders; 

You expect to see the data sorted by customer and order ID, but you don't. Can you identify the bug in the code and suggest a solution?