The UNPIVOT operator is the opposite of the PIVOT operator. As I explained in
my earlier post
, the PIVOT operator takes a normalized table and transforms it into a new table where the columns of the new table are derived from the values in the original table. The UNPIVOT operator takes a pivoted table and transforms it back into a normalized form with one row per data point using the column names as values in the result. For example, suppose we have the following data:
This is the output of the PIVOT operation from my earlier posts. There is one row for each employee with up to three years of sales data per row. If there is no sales data for a particular employee for a particular year, we simply insert NULL. We can transform this table back to its original form with a single row per employee per year using the following UNPIVOT statement:
SELECT EmpId, CAST (Yr AS INT) AS Yr, Sales
FROM (SELECT EmpId, , ,  FROM PIVOT_Sales) AS p
UNPIVOT (Sales FOR Yr IN (, , )) AS s
I've explicitly casted the
column to integer. The default type for the
is NVARCHAR(128). This type is based on the maximum column name length of 128 characters.
Unlike PIVOT operations which may not be reversible, all UNPIVOT operations are reversible (so long as all of the input data is preserved). That is, we can always transform the output of an UNPIVOT operation back into the original table using an appropriate PIVOT operation. Unlike PIVOT operator, the UNPIVOT operator does not require or support aggregation functions.
This query plan simply takes each row of the input table and joins it with a constant scan that generates three rows - one for each of the three columns listed in the UNPIVOT IN clause - for each input row. The plan then filters out any rows that have NULL data. (Note that [Expr1008] is the
column and [Expr1009] is the
column.) There are a couple of points worth noting about this query plan. First, the join must be a nested loops join because the constant scan operator uses the correlated parameters from the outer side of the join to generate rows. There is no way to generate these rows without these correlated parameters. Second, the join need not be a left outer join. The constant scan always produces exactly three rows and, thus, the outer rows always join and are never NULL extended. Nevertheless, the outer join is harmless in this context and behaves like an inner join.
Note that we can write the original query as:
SELECT p.EmpId, Yr, Sales
FROM PIVOT_Sales AS p CROSS APPLY
SELECT EmpId, 2005 AS Yr,  AS Sales UNION ALL
SELECT EmpId, 2006,  UNION ALL
SELECT EmpId, 2007, 
) AS s
WHERE Sales IS NOT NULL
This query yields a nearly identical query plan. The UNION ALL syntax produces a similar result to the constant scan except that there are now three constant scans and a concatenation operator:
|--Filter(WHERE:([Union1007] IS NOT NULL))
|--Nested Loops(Inner Join, OUTER REFERENCES:([p]., [p]., [p].))
|--Table Scan(OBJECT:([tempdb].[dbo].[PIVOT_Sales] AS [p]))
In this plan, [Union1007] is the
column. We can actually see the definition of [Union1007] from the
column of the concatenation operator in the SET SHOWPLAN_ALL ON output:
Notice that the values for [Union1007] are actually derived directly from the correlated parameters of the cross apply (from the
table) and not from the constant scans. [Union1006] is the
column and the values are derived from the constant scans.