Resources For IT Professionals
Post an article
Wikis - Page Details
  • First published by (1Microsoft Partner, Microsoft Community Contributo)
  • When: 20 Jul 2012 9:00 AM
  • Last revision by (aMicrosof)
  • When: 12 Oct 2012 2:44 PM
  • Revisions: 7
  • Comments: 2
Options
RSSSubscribe to Article (RSS)
Can You Improve This Article?
Positively! Click Sign In to add the tip, solution, correction or comment that will help other users.

Report inappropriate content using these instructions.
Wiki  >  TechNet Articles  >  SSIS Pivot Transformation

SSIS Pivot Transformation

SSIS Pivot Transformation

Integration Services uses transformations to manipulate data during an ETL dataflow. Transformations can be used in [[articles:SSIS Dataflow Task|Data Flow Tasks]], between data source and destination components, or other transformations.

There are many cases (for example, in reporting), when some elements of a dataset has to be transposed. The Pivot transformation can help in this during the load process of a data warehouse.

For example, if the source dataset looks like this (three columns):




Then, with using the Pivot transformation, it can be transposed like this:


The transformation does not have a simple editor. It can be configured in the Advanced Editor of the transformation.
In this case, in the Input Columns page of the Advanced Editor, all columns had been checked in:


On the Input and Output Properties page, the PivotUsage of all input columns had been changed: 1 for Period, 2 for Metric Name and 3 for Amount. The options are the following:
 All output columns must be configured and mapped to one of the original columns manually. This can be done by setting the PivotKeyValue and the SourceColumn property of all new columns:
In this case, for the new column called Profit, the PivotKey value needs to be Profit, and the SourceColumn value is 111, which is the LineageID value of the Amount input column:


 This transformation supports an input, a regular and an error output. It looks like this in the data flow editor of BIDS (PVT Pivot):



This is a partially blocking transformation because of the introduction of completely new buffers.

Since there is no one-to-one relationship between the input and output buffers, this is an asynchronous transformation.

So, the Pivot transformation can be used as an opposite transformation of the Unpivot transformation, which is helpful when a more normalized result set is needed.

For more detailed information on this transformation, please refer to this MSDN article.
For the complete list of SSIS transformations, check this Wiki article.


Other Languages

This article is also available in the following languages:

 

, , , , , , , , , , [Edit tags]
Leave a Comment
  • Please add 5 and 6 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Posted by
    on 20 Jul 2012 9:01 AM

    This page should be updated to SQL 2012 ASAP.

  • Posted by
    on 29 Aug 2012 4:26 PM

    Zoltán Horváth edited Revision 1. Comment: adding Other Languages, tagging

Page 1 of 1 (2 items)