Professional SQL Server™ 2005 Integration Services

6.12. Unpivot

The Unpivot transform performs the reverse of the Pivot transform. Use this transform to reengineer pivoted data quickly into a relational state. All the same ideas about pivots apply, but you'll be applying them in reverse. Use a view in the AdventureWorks works database named [Sales].[vSalespersonsalesbyfiscalyears] to demonstrate returning a pivot table back into its named columns. See Figure 6-32 for a sample set of the rows in this pivot table.

Figure 6.32. Figure 6-32

Your task, as with the Pivot transform, is to break the columns down into their pivot functions. Which column appears to define the row? The column labeled "SalesPersonID" is the best candidate for your row (or set) column since it meets the criteria of uniquely defining the row. The columns labeled "2002," "2003," and "2004" appear to be items in a series and meet the requirement of unique instances of columns. Columns [2002], [2003], and [2004] are the best candidates to be combined into your Unpivot column. Intuitively, if you are combining three different named columns, you'll need only one column and one column name. Name your Unpivot column "SalesYear." Determining the value column is not a function of choosing an available column heading. The value column is defined by the data in the matrix—the result of the row and pivot column combinations. Since your data is sales data, you can assume ...