Specifying a horizontal pivot operation and mapping output columns

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

Use horizontal pivot operations to map rows into output columns.
  1. Design your basic job by doing the following tasks:
    1. Place a Pivot Enterprise stage on your job design 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 and click the Properties tab.
  4. Specify the Pivot type as Horizontal on the Pivot Action tab.
  5. Specify the horizontal pivot operation on the Pivot Definitions tab of the Stage page by doing the following tasks:
    1. In the Name field, type the name of the output column that will contain the pivoted data (the pivot column).
    2. Specify the SQL type and, if necessary (for example if the SQL type is decimal), the length and scale for the pivoted data.
    3. Double-click the Derivation field to open the Column Selection window.
    4. In the Available Columns list, select the columns that you want to combine in the pivot column.
    5. Click the right arrow to move the selected column to the Selected Columns list.
    6. Click OK to return to the Pivot tab.
    7. If you want the stage to number the pivoted rows by generating a pivot index column, select Pivot Index.
  6. Click the Output page to go to the Mapping tab.
  7. Specify your output data by doing the following tasks:
    1. Select your pivot column or columns from the Columns table in the right pane and drag them over to the output link table in the left pane.
    2. Drag all the columns that you want to appear in your output data from the available columns on the left side of the window to the available output columns on the right side of the window.
    3. Click the Columns tab to view the data that the stage will output when the job runs.
  8. Click OK to save your pivot operation and close the Pivot Enterprise stage.