Pivot Transform
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.
- Have a glance on source data
- 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.
- 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.
