Pivot Enterprise stage

Pivot Enterprise stage:

The Pivot Enterprise stage is a processing stage that pivots data horizontally and vertically.

Pivot Types:

1)Horizontal
2)Vertical

Horizontal pivoting maps a set of columns in an input row to a single column in multiple output rows.
The output data of the horizontal pivot action typically has fewer columns, but more rows than the input
data.

Input Data:

CUSTOMER_ID	CUSTOMER_NAME	JAN_EXPENSE	FEB_EXPENSE	MAR_EXPENSE
1	UMA	100	200	300
2	POOJITHA	200	300	400

Output Data:

CUSTOMER_ID	CUSTOMER_NAME	Q1EXPENSE
1	UMA	100
1	UMA	200
1	UMA	300
2	POOJITHA	200
2	POOJITHA	300
2	POOJITHA	400

Specify a horizontal operation in Pivot enterprise stage.

Create basic datastage job using sequential file as input stage to read input data,Pivot Enterprise stage and sequential file stage as output to create output data.

Basic Job Design:

Configure sequential file stage as shown below to read input data(comma delimited input file).

Step2)Configure pivot enterprise stage.Double click on pivot enterprise stage and configure shown like below.

*Go to the properties page and select Pivot Type = Horizontal

Click on Pivot Properties tab on stage properties page and follow following steps to configure for Horizontal pivot:

You can generate a pivot index that will assign an index number to each row within sets of horizontally
pivoted data:

Click on the above highlighted red circle.

Now Click on the Mapping tab of the output page:

Now configure Output sequential file stage to create a delimited output file as shown below.

Compile the job:

Run the job:

Output:

vertical pivot operation:

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

Input Data:

CUSTOMER_ID	CUSTOMER_NAME	Q1EXPENSE
1	UMA	100
1	UMA	200
1	UMA	300
2	POOJITHA	200
2	POOJITHA	300
2	POOJITHA	400

Output Data:

CUSTOMER_ID	CUSTOMER_NAME	JAN_EXPENSE	FEB_EXPENSE	MAR_EXPENSE	Q1AVERAGE
1	UMA	100	200	300	200
2	POOJITHA	200	300	400	300

Now open Pivot enterprise stage and go the properties tab on stage page:

Go to pivotproperties tab on the stage page and click load button to load metadata:

Now specify array size as 3 because we are have 3 months expenses in the quarter expense of in the input data and we need to convert into 1 row.

Configure Mapping tab in the output page of the stage.

Compile and run the job: