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
- 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.
- 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.
- 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.
- 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.
-
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.
- 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:
|
| 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 |
