Vertical Pivot operation:

It is used to map 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.


Here, we are having Car.txt as our input where there are 3 columns as an input and we would like to pivot the data on Company and Sales_Q columns so that we can have only multiple columns for the same which means less no. of rows. We are also going to apply aggregate functions for our example Max(Sales_Q)

We are going to set multiple input rows to a single row/rows (for particular column). The main advantage of this stage is we can use aggregation functions like avg, sum, min, max, first, last etc. for pivoted column.  

Design the job as shown below:

p6 - Vertical Pivot Stage Example

Input Data:

pivot Input - Vertical 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.

p1 - Vertical 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, select the checkbox for column which need to be pivoted and column to group by as shown below.

Note: Vertical Pivot will require at least one column to pivot and one column to perform group by on and provide the Array size as required. 

p2 - Vertical Pivot Stage Example

Under StageàOutput tab of Pivot stage properties tab, map the input columns to the output columns as per the requirements. 

p3 - Vertical 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.

p4 - Vertical Pivot Stage Example

Output Data:

Vertical Pivot Result - Vertical Pivot Stage Example