Pivot enterprise stage in DataStage

Reading Time: 4 minutes

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