Specifying a vertical pivot operation and mapping output columns (DataStage®)

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

About this task

You can configure a pivot operation to vertically pivot data and then map the resulting columns onto output columns.

1. Design your basic job by doing the following tasks:
   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 Vertical .
5. Click Edit in the Pivot Properties section.
6. Specify the vertical pivot operation on the Vertical pivot page.
   1. Click Add column +.
   2. In the Column name field, type the name of the column that you want to group or pivot.
   3. Select GroupBy for each column that you want to group in the output.
   4. Select Pivot for each column that you want to pivot from rows to columns. You cannot pivot columns that have GroupBy selected.
   5. Select an aggregation function from Aggregations when pivoting a column.
   6. Click Apply.
   7. Specify the SQL type and if necessary the length and scale for the pivoted data.
   8. Click Apply and Return to return to the Stage tab.
   9. If you want the stage to generate a pivot index column to number the pivoted columns, 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.