Specifying a horizontal pivot operation and mapping output columns (DataStage)

You can specify the horizontal pivot operation and then map the resulting columns onto output columns.

About this task

Use horizontal pivot operations to map rows into output columns. For an example of the results, see Example of horizontally pivoting data (DataStage).

1. Design your DataStage® flow:
   1. Drag a Pivot Enterprise stage onto the canvas.
   2. Place the stage that will supply the input data on the canvas on the left of the Pivot Enterprise stage.
   3. Place the stage that is the target for the output data on the canvas on the right of the Pivot Enterprise stage.
   4. Link the three stages together.
2. Configure the stage that provides the input data.
3. Open the Pivot Enterprise stage, then open the Properties section on the Stage tab.
4. Specify the Pivot type as Horizontal.
5. Click Edit in the Pivot Properties section.
6. Specify the horizontal pivot operation on the Horizontal pivot page.
   1. Click Add column +.
   2. In the Column name field, type the name of the output column that will contain the pivoted data (the pivot column).
   3. Specify the SQL type and, if necessary (for example if the SQL type is decimal), the length and scale for the pivoted data.
   4. Select the columns that you want to combine in the pivot column by using the drop-down list of available columns under Derivation.
   5. Click Apply.
   6. If you want the stage to number the pivoted rows by generating a pivot index column, select Pivot index.
7. Specify your output data by doing the following tasks:
   1. Click the Output tab.
   2. Open the Columns section, then click Edit by the Columns table.
   3. Select all the columns that you want to appear in your output data, then click Apply and return.
   4. Click the Columns tab to view the data that the stage will output when the job runs.
8. Click Save to save your pivot operation and close the Pivot Enterprise stage.