What do you think of this post?
  • Awesome 
  • Interesting 
  • Useful 

-- Download Pivot Transform as PDF --


Note on Pivot: This creates a new row for every value that you specified as a pivot column. Observe the icon, it says that will convert column to rows.

Options:

  • Pivot_Sequence : It creates a sequence for each row created from a pivoted column
  • Non Pivot : List of columns specified here by you, those will be displayed as it is in the target.
  • Pivot Sets : For each n every pivot set, you can define a set of columns. For each set you will be having a Header column and a Data column. Header column consists all the pivoted columns, and Data column contains the actual data in the pivoted columns.
  • Pivot Columns : Set of columns swivelled to rows.
Design Steps: Having 5 columns in the source table(Sno, Sname, Jan_sal, Feb_sal, Mar_sal). I want to convert salary column values in to rows
  • Drag the source table and target table from Datastore object library on to the workspace, drag the Pivot transform and place in between your source and target. Now, connect each object as shown in the below figure.

pivot1

  • Have a glance on source data
Pivot_Source_Table
  • Give a dbl. click on the Pivot Transform. Check the Pivot sequence name, by default “PIVOT_SEQ” will be there. If you want you can change or leave as it is. Now I want to load Sno, Sname as it is. So i have dragged these two columns on to the Non-pivotal list.
  • Now drag all SAL Columns on to the Pivotal list. Default PIVOT_DATA, PIVOT_HDR names will be ge generated.
Pivot_Transform
  • Save the definition, now you can see, (SNO, SNAME, PIVOT_SEQ, PIVOT_HDR, PIVOT_DATA) columns in Schema Out.
  • Come out from the Pivot tranform by pressing BACK button on the standard tool bar.
  • Save the Dataflow, validate it and execute the job.
  • Check out the resultant data.
Pivot_Target_Data

What do you think of this post?
  • Awesome 
  • Interesting 
  • Useful 

2 comments on “Pivot Transform

  1. What is the business requirement when we need to use this transform ?
    what is the context where we use this transform ??

  2. I’ve used this many times.. for example, we have several files where the import data comes in hits per month by vendor, where months are columns and vendor is static on each column.

    In order to transform this into a standard table [vendor, month_date, number of hits] —

    You would use this transform.

Leave a Reply to Krishnaprasad Cancel reply

Your email address will not be published. Required fields are marked *


*