Specifying a vertical pivot operation and mapping output columns
You can specify the vertical pivot operation and then map the resulting columns onto output columns.
You can configure a pivot operation to vertically pivot data and then map the resulting columns onto output columns.
- Design your basic job by doing the following tasks:
- Drag a Pivot Enterprise stage onto the canvas.
- Place the stage that will supply the input data on the canvas on the left of the Pivot Enterprise stage.
- Place the stage that is the target for the output data on the canvas on the right of the Pivot Enterprise stage.
- Link the three stages together.
- Configure the stage that provides the input data.
- Open the Pivot Enterprise stage, then open the Properties section on the Stage tab.
- Specify the Pivot Type as Vertical .
- Click Edit in the Pivot Properties section.
Specify the vertical pivot operation on the Vertical pivot page.
- Click Add column +.
- In the Column name field, type the name of the column that you want to group or pivot.
- 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.
- Select an aggregation function from Aggregations when pivoting a column.
- Click Apply.
- Specify the SQL type and if necessary the length and scale for the pivoted data.
- Click Apply and Return to return to the Stage tab.
- If you want the stage to generate a pivot index column to number the pivoted columns, select Pivot Index.
- Specify your output data by doing the following tasks:
- Click the Output tab.
- Open the Columns section, then click Edit by the Columns table.
- Select all the columns that you want to appear in your output data, then click Apply and Return.
- Click the Columns tab to view the data that the stage will output when the job runs.
- Click Save to save your pivot operation and close the Pivot Enterprise stage.