New Relational Operators: PIVOT, UNPIVOT, and APPLY | Microsoft SQL Server 2005: Changing the Paradigm (SQL Server 2005 Public Beta Edition)

New Relational Operators: `PIVOT`, `UNPIVOT`, and `APPLY`

If you have ever worked with Microsoft Access, you might have used the trANSFORM statement to create a crosstab query. Similar functionality is provided by SQL Server 2005 via the PIVOT operator with the SELECT statement. The PIVOT operator can be used to transform a set of rows into columns. The UNPIVOT operator complements the PIVOT operator by allowing you to turn columns into rows.

The following simple example illustrates the use of the PIVOT and UNPIVOT operators:

USE AdventureWorks; GO IF OBJECT_ID('dbo.tblCars') IS NOT NULL    DROP TABLE dbo.tblCars; GO CREATE TABLE dbo.tblCars    (RecordID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,    DealerID INT NOT NULL,    Make NVARCHAR(50),    MakeYear SMALLINT,    CarsSold INT); GO INSERT INTO dbo.tblCars SELECT 1, 'Honda', 2003, 100; INSERT INTO dbo.tblCars SELECT 2, 'Toyota', 2003, 500; INSERT INTO dbo.tblCars SELECT 2, 'Honda', 2003, 200; INSERT INTO dbo.tblCars SELECT 1, 'Honda', 2004, 200; INSERT INTO dbo.tblCars SELECT 1, 'Toyota', 2004, 600; INSERT INTO dbo.tblCars SELECT 2, 'Honda', 2004, 300; INSERT INTO dbo.tblCars SELECT 2, 'Toyota', 2005, 50; GO SELECT * FROM dbo.tblCars; GO SELECT Make, [2003], [2004], [2005] FROM    (       SELECT Make, CarsSold, MakeYear FROM dbo.tblCars    ) tblCars    PIVOT (SUM(CarsSold) FOR MakeYear IN ([2003],[2004], [2005])) tblPivot; GO

This script creates a sample table to store car sales data. It then inserts some sample data into that table, as shown here:

RecordID    DealerID    Make    MakeYear CarsSold ----------- ----------- ------- -------- --------- 1           1           Honda   2003     100 2           2           Toyota  2003     500 3           2           Honda   2003     200 4           1           Honda   2004     200 5           1           Toyota  2004     600 6           2           Honda   2004     300 7           2           Toyota  2005     50

The PIVOT statement transforms rows into columns, at the same time calculating the total sales per year. It produces the following results:

Make    2003   2004   2005 ------- ------ ------ ------ Honda   300    500    NULL Toyota  500    600    50

Here's how you save this pivoted data into a table and then use UNPIVOT to transform columns into rows:

IF OBJECT_ID('dbo.tblPivotData') IS NOT NULL    DROP TABLE dbo.tblPivotData; GO SELECT Make, [2003], [2004], [2005] INTO dbo.tblPivotData FROM    (       SELECT Make, CarsSold, MakeYear FROM dbo.tblCars    ) tblCars    PIVOT (SUM(CarsSold) FOR MakeYear IN ([2003],[2004], [2005])) tblPivot; GO SELECT Make, tblUnPivot.MakeYear, tblUnPivot.CarsSold FROM dbo.tblPivotData      UNPIVOT      (CarsSold for MakeYear in ([2003],[2004], [2005])) tblUnPivot; GO

Using the UNPIVOT clause results in the following output:

Make    MakeYear   CarsSold ------- ---------- ----------- Honda   2003       300 Honda   2004       500 Toyota  2003       500 Toyota  2004       600 Toyota  2005       50

Refer to the pivoted data shown earlier (with 2003, 2004, and 2005 as the columns). All UNPIVOT does is convert columns into rows.

The two common applications of the PIVOT operator are to create an analytical view of the data, as illustrated previously, and to implement an open schema, as illustrated here:

IF OBJECT_ID('dbo.tblServers') IS NOT NULL    DROP TABLE dbo.tblServers; GO CREATE TABLE dbo.tblServers(  ServerID INT IDENTITY(1,1),  ServerName VARCHAR(50)); GO INSERT INTO dbo.tblServers SELECT 'Server_1'; INSERT INTO dbo.tblServers SELECT 'Server_2'; INSERT INTO dbo.tblServers SELECT 'Server_3'; GO IF OBJECT_ID('dbo.tblServerEquip') IS NOT NULL    DROP TABLE dbo.tblServerEquip; GO CREATE TABLE dbo.tblServerEquip(    ServerID INT,    EquipmentType VARCHAR(30),    Description VARCHAR(100)); GO INSERT INTO dbo.tblServerEquip SELECT 1,'Hard Drive','40GB'; INSERT INTO dbo.tblServerEquip SELECT 1,'Memory Stick','512MB'; INSERT INTO dbo.tblServerEquip SELECT 2,'Memory Stick','512MB'; INSERT INTO dbo.tblServerEquip SELECT 2,'Hard Drive','40GB'; INSERT INTO dbo.tblServerEquip SELECT 2,'NIC','10 MBPS'; INSERT INTO dbo.tblServerEquip SELECT 3,'Memory Stick','512MB'; INSERT INTO dbo.tblServerEquip SELECT 3,'Hard Drive','40GB'; INSERT INTO dbo.tblServerEquip SELECT 1,'Hard Drive','100GB'; INSERT INTO dbo.tblServerEquip SELECT 1,'NIC','10 MBPS';I INSERT INTO dbo.tblServerEquip SELECT 1,'NIC','1GB Fibre Channel'; GO SELECT * FROM    ( SELECT s.ServerName, se.EquipmentType, se.Description      FROM dbo.tblServers s      INNER JOIN dbo.tblServerEquip se      ON s.ServerID = se.ServerID    ) AS pnt    PIVOT    (       COUNT(Description)       FOR EquipmentType IN([Hard Drive], [Memory Stick], [NIC])    ) AS pvt; GO

Using the PIVOT command produces the following output:

ServerName    Hard Drive  Memory Stick NIC ------------- ----------- ------------ ----------- Server_1      2           1            2 Server_2      1           1            1 Server_3      1           1            0

This script creates a table to store all the servers in a production environment and another table to store server equipment type. The PIVOT operator is used to join these two tables to create a simplified view that shows the server name and counts of the equipment in that server.

The third new operator, APPLY, can be used to invoke a table-valued function for each row in the rowset. A table-valued function is a function that returns a rowset (that is, a table) as a return value.

For each row in the outer query in which the APPLY operator is used, the table-value function is called, and the columns returned by the function are appended to the right of the columns in the outer query, to produce a combined final output. You can pass the columns from the outer query as the parameters to the table-valued function specified with the APPLY operator. Here is an example of using the APPLY operator:

IF OBJECT_ID('dbo.GetEmpHierarchy') IS NOT NULL    DROP FUNCTION dbo.GetEmpHierarchy; GO CREATE FUNCTION dbo.GetEmpHierarchy (@EmployeeID AS INT) RETURNS TABLE AS    RETURN       WITH RecCTE(ManagerID, EmployeeID, EmployeeLevel) AS       (           SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel              FROM HumanResources.Employee              WHERE EmployeeID = @EmployeeID           UNION ALL           SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1              FROM HumanResources.Employee e              INNER JOIN RecCTE r              ON e.ManagerID = r.EmployeeID       )       SELECT EmployeeID, ManagerID, EmployeeLevel       FROM RecCTE; GO SELECT s.SalesPersonID, s.SalesOrderID, s.CustomerID, s.TotalDue, tvf.* FROM Sales.SalesOrderHeader AS s      CROSS APPLY dbo.GetEmpHierarchy(s.SalesPersonID) AS tvf ORDER BY TotalDue DESC; GO

This script defines a table-valued function that accepts EmployeeID as an input parameter and returns a table by using a recursive CTE (as discussed earlier in this chapter, in the section "Common Table Expressions (CTEs)") to return the hierarchy for that employee. The APPLY operator is used in the SELECT statement so that for each row in the Sales.SalesOrderHeader table, the query returns the employees directly or indirectly reporting to the current SalesPersonID. If you run the script in the AdventureWorks sample database and scroll down in the result set grid, you see some Sales.SalesOrderHeader rows repeated with the employee hierarchy shown, using the three columns on the right side.

APPLY can take two forms: CROSS APPLY and OUTER APPLY. With CROSS APPLY, if the tablevalued function returns an empty set for a given outer row, that outer row is not returned in the result. On the other hand, OUTER APPLY also returns rows from the outer table for which the table-valued function returned an empty set. As with OUTER JOINs, NULL values are returned as the column values that correspond to the columns of the table-valued function.