Professional SQL Server™ 2005 Integration Services

6.11. Pivot Transform

Do you ever get the feeling that pivot tables are the modern day Rosetta Stone for translating data to your business owners? You store it relationally, but they ask for it in a format that you have to write a complex case statement to generate. Well, not anymore. Now you can use a SSIS transformation to generate the results. A pivot table is a result of cross-tabulated columns generated by summarizing data from a row format. Prior to SQL Server 2005, a pivot table could be generated only by using a SELECT...CASE statement to build summary columns based on one field in the row.

Typically a Pivot is generated using the following input columns:

• Pivot Column: A Pivot column is the element of input data to "pivot." The word "pivot" is another way of saying "to create a column for each unique instance of." However, this data must be under control. Think about creating columns in a table. You wouldn't create 1000 uniquely named columns in a table. So for best results when choosing a data element to pivot, pick an element that can be run through a GROUP BY statement that will generate 15 or fewer columns. If you are dealing with dates, use something like a DATENAME function to convert to the month or day of the year

• Row Columns: Row columns are elements of input data that act as row (not column) identifiers. Just like any GROUP BY statement, some of the data are needed to define the group (row), whereas other data are just along for the ride.

• Value Columns: These columns ...