Pivot Stage Example

Pivot enterprise stage is one of a processing stage which pivots data vertically and horizontally depending upon the user requirements. There are two types of Pivot:

1. Horizontal Pivot:

Horizontal Pivot operation maps input columns to the multiple rows, resulting in more rows than the input data. Basically we can map several sets of input columns to several output rows. 

2. Vertical Pivot:

Vertical pivot operation maps a set of rows in the input data to single or multiple output columns. The array size determines the number of rows in the output data. The output data of the vertical pivot action typically has more columns, but fewer rows than the input data.

 

Example:

Horizontal Pivot operation:

Here, we are having Car.txt as our input where there are 5 columns as an input and we would like to pivot the data on CarSegment column so that we can have only multiple rows for the same which means only 2 columns i.e. CarSegment and Company as an output.

Design the job as shown below:

p6 - Pivot Stage Example

Input Data:

pivot1 - Pivot Stage Example

Open the Properties window by double click or Right clickàProperties from the drop down menu. Under StageàProperties tab select Pivot type as Horizontal, similar to below snapshot.

pivot3 - Pivot Stage Example

Next, Go to Pivot Properties tab and select the Checkbox ‘Pivot Index’ which is situated down to the left corner of the window. Pivot Index will appear as shown below.

Pivot index can be used to get Pivot value for that column in target.

Click on Load button down to right corner, to load the column definitions required in output.

Next, Edit the Derivation field to Pivot on Company by double clicking on Company derivation. A Column selection dialog will popup, select the columns need to pivoted as below.

pivot6 - Pivot Stage Example

Click Ok; we will be able to see the derivation populated under derivation option as below.

pivot2 - Pivot Stage Example

Input columns will look like below under StageàInput tab of Pivot stage properties.

pivot4 - Pivot Stage Example

Under StageàOutput tab of Pivot stage properties tab, map the input columns to the output columns as per the requirements. As in output we need to pivot input columns to multiple rows i.e. Pivot (Company) hence we have pulled CarSegment and Pivot(Company) as Company as shown below.

pivot5 - Pivot Stage Example

Save, Compile and run the job. Post successful completion of the job the output will contain more no. of rows as below.

Output Data:

pivot Horizontal Result - Pivot Stage Example