Modeling Guide

Pivot

Creates a row of data from existing rows.

Context

Use this node to combine data from several rows into one row by creating new columns. A pivot table can help summarize the data by placing it in an output data set. For each unique value in a pivot axis column, it produces a column in the output data set.

Procedure

  1. In the Axis Attributes section, click Click to select axis. Select the column that you want to pivot on, and then click Select. The value of the axis represents a particular set of output columns. A set of pivoted columns is generated for each axis value. Create an axis value for each unique value in the axis column. At runtime, a new column is created for each pivoted attribute and each unique axis value in the Axis Attribute section.
  2. Click Add Values to create one or more columns to hold the pivoted data. Type the column name in the Value column, and enter a prefix in the Prefix column. The new columns with their prefixes are displayed in the list of output columns after you specify the data in the columns. An underscore is added automatically to separate the prefix name from the pivoted column name. For example, if you choose a SALARY column as the axis, you might want to enter a year value such as 2017 and a prefix of 2017 and another value for 2016.
  3. Under Data Columns, select the + icon to select a column whose values you want pivoted from rows into columns. For example, if you have BONUS column that you wanted included with the SALARY data. Notice that the Output Columns section has a 2017_BONUS entry.
  4. Under Output Columns, click Pass Through to select the columns that you want to output without pivoting. The pass-through columns appear in the target table without modification.
  5. Set the Duplicate Strategy to choose the behavior when a duplicate is encountered.
    • Select Abort when you want to cancel the transform process.
    • Select First Row when you want to store the value in the first row.
  6. Click Apply to return to the flowgraph.

Example

Suppose that you have employee contact information, and you must identify those records with missing data.

Employee_ID Contact_Type Contact_Name Contact_Address Phone
2178 emergency Shane McMillian 404 Walnut St. 555-1212
2178 home Bradly Smith 2168 Park Ave. S. 555-8168
2178 work Janet Garcia 801 Wall St. 555-7287
7532 emergency Adam Ellis 7518 Windmill Rd. 555-2165
7532 home Sarah La Fonde 2265 28th St. SW 555-1010
1298 work Ravi Rahim 801 Wall St. 555-7293

Because there are several rows for each employee, finding missing information may be difficult. Use the Pivot node to rearrange the data into a more searchable form without losing any category information. Set the properties as follows.

Option Value Notes
Axis column Contact_Type Shows the order of the pivot.
Axis value emergency

home

work

Select these three fields so the names and numbers of the contacts are output into a single row for each employee.
Column prefix emergency

home

work

Enters a prefix to the column headings. In this case, the column names are:
  • Emergency_Contact_Name
  • Emergency_Phone
  • Home_Contact_Name
  • Home_Phone
  • Work_Contact_Name
  • Work_Phone
Data Columns Contact_Name

Contact_Phone

Moves the selected data into additional columns. These are the values in the Contact_Type column in the source table
Pass through Employee_ID Choose Employee_ID as a column that will not be pivoted. In this case, this column is output in a single row.
Duplicate strategy First Row If a duplicate is found during processing, only the first record is output, and processing continues. Choosing Abort causes the processing to fail.

The output data set includes the Employee_ID (not pivoted) and the Contact_Name and Phone fields for each pivot Axis Value (emergency, home, and work). In cases where the data is empty in the source, the Pivot node stores a null value.

The result is a single row for each employee, which you can use to search for missing contact information.

Employee_ID Emergency_Contact_Name Emergency_Phone Home_Contact_Name Home_Phone Work_Contact_Name Work_Phone
2178 Shane McMillian 555-1212 Bradly Smith 555-8168 Janet Garcia 555-7287
7532 Adam Ellis 555-2165 Sarah La Fonde 555-1010 Null Null
1298 Null Null Null Null Ravi Rahim 555-7293