O'Reilly logo

Professional SQL Server™ 2005 Integration Services by Mike Murphy, Haidong Ji, Jason Gerard, Erik Veerman, Andy Leonard, Kathi Kellenberger, Douglas Hinson, Darren Green, Allan Mitchell, Brian Knight

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required