Sql server® 2012 t-sql fundamentals

Unpivoting with the native T-SQL 

UNPIVOT

 Operator

Unpivoting data involves producing two result columns from any number of source columns that you 

unpivot. In this example, you need to unpivot the source columns A, B, C and D, producing two result 

columns called custid and qty. The former will hold the source column names (A, B, C, and D), and the 

latter will hold the source column values (quantities in this case). SQL Server supports a very elegant, 

minimalistic native UNPIVOT table operator. The general form of a query with the UNPIVOT operator 

is as follows.

SELECT ... 

FROM  

  UNPIVOT( 

    FOR IN()) AS  

 

...;

Like the PIVOT operator, UNPIVOT was also implemented as a table operator in the context of the 

FROM clause. It operates on a source table or table expression (EmpCustOrders in this case). Within 

the parentheses of the UNPIVOT operator, you specify the name you want to assign to the column 

that will hold the source column values (qty here), the name you want to assign to the column that 

will hold the source column names (custid), and the list of source column names (A, B, C, and D). Fol-

lowing the parentheses, you provide an alias to the table resulting from the table operator. 

www.it-ebooks.info

---

232  

Microsoft SQL Server 2012 T-SQL Fundamentals

Here’s the complete solution query that uses the UNPIVOT operator to satisfy the unpivoting 

request in the example.

SELECT empid, custid, qty 

FROM dbo.EmpCustOrders 

  UNPIVOT(qty FOR custid IN(A, B, C, D)) AS U;

Note that the UNPIVOT operator implements the same logical processing phases described earlier—

generating copies, extracting elements, and eliminating NULL intersections. The last phase is not an 

optional phase as in the solution based on standard SQL.

Also note that unpivoting a pivoted table cannot bring back the original table. Rather, unpivoting 

is just a rotation of the pivoted values into a new format. However, the table that has been unpivoted 

can be pivoted back to its original pivoted state. In other words, the aggregation results in a loss of 

detail information in the original pivoting. After the initial pivot, all the aggregations can be preserved 

between the operations, provided that the unpivot does not lose information.

When you are done, run the following code for cleanup.

IF OBJECT_ID('dbo.EmpCustOrders', 'U') IS NOT NULL DROP TABLE dbo.EmpCustOrders;