Pivot enterprise stage in DataStage

Pivot enterprise stage is a processing stage which pivots data vertically and horizontally depending upon the requirements. one input link and one output link.

There are two types

1. Horizontal - 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

Horizontal Pivot Operation:

Specify the Pivot type as Horizontal on the Pivot Action tab.

Specify the horizontal pivot operation on the Pivot Definitions tab of the Stage page by doing the following tasks: 

a. In the Name field, type the name of the output column that will contain the pivoted data (the pivot column).

b. Specify the SQL type and, if necessary (for example if the SQL type is decimal), the length and scale for the pivoted data.

c. Double-click the Derivation field to open the Column Selection window.

d. In the Available Columns list, select the columns that you want to combine in the pivot column.

e. Click the right arrow to move the selected column to the Selected Columns list.

f. Click OK to return to the Pivot tab.

g. If you want the stage to number the pivoted rows by generating a pivot index column, select Pivot Index.

Please see the below example for horizontal pivot operation

Input Data:

CUST_NAME  JAN_EXPENSE  FEB_EXPENSE  MAR_EXPENSE

John                    100                       200                      300

Joe                       200                       300                     400

Output Data:

CUST_NAME    Q1_EXPENSE

John                       100

John                       200

John                       300

Joe                         200

Joe                         300

Joe                         400

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

Vertical Pivot Operation:

Select the Pivot Type property and select Vertical for the Pivot Type

Select GroupBy for each column that you want to group in the output.

Select Pivot for each column that you want to pivot from rows to columns. You cannot pivot

columns that have GroupBy selected.

Double-click the Aggregation functions required for this column field to open the Pivot -

Function Select window.

Specify the Array Size to specify the number of sets of pivoted data that will be generated for each output row. For example, specify an array size of 7 to generate a row of pivoted data in 7 columns, with each column for a day of the week.

Please see the below example for vertical pivot operation

Input Data:

CUST_NAME    Q1_EXPENSE

John                       100

John                       200

John                       300

Joe                         200

Joe                         300

Joe                         400

Output Data:

CUST_NAME  JAN_EXPENSE  FEB_EXPENSE  MAR_EXPENSE

John                    100                       200                      300

Joe                       200                       300                     400