UNPIVOT reorganizes the structure of a table by taking related columns of data and moving them into different rows.

UNPIVOT follows these rules:

Creates the new columns that you define. Tamr Core creates primary keys for them. See Primary Key Management. If no value is defined for a column, then it is null.
Carries over all columns that are not mentioned in its definition. Specifically, unpivot carries over all columns except for the columns that you explicitly specify, and the columns created by the expressions. To keep some columns at the left side of the table, such as table keys, mention them explicitly when you define the unpivot.

Example

In the following example, the input datasets have attributes for the spend total in each quarter, Q1, Q2, Q3, and Q4. You can use the Unpivot option to reorganize this table and use the values in these four attributes to populate two new unified attributes: one for the Quarter in which the spend occurred, and the other for the amount of Spend.

1708 — Before unpivot, the customer is represented by one record in the dataset, and each quarter of spend is one column.

In this example, you add the transformation, select Unpivot, and then select the attributes you want to take values from: Q1, Q2, Q3, and Q4. You then set the Variable attribute to Quarter and the Value attribute to Spend.

786 — The completed transformation editor for this unpivot example.

When you preview this transformation, the Quarter and Spend attributes are filled with the information from the original dataset. The attributes from which the data was extracted are emptied. Any other unified attributes are preserved for each record, such as fullname. The result of using the Unpivot option in this example is that the number of records is four times larger than in the original dataset.

1702 — The customer's spend by quarter is now represented by four records, each with a different value for the Quarter attribute.

Unpivoting Additional Attributes

When you need to unpivot multiple attributes together, you can add dependent columns. One common use case is when the variable attribute contains data with different units. In this case, you can add the units column as a dependent column.

520 — The transformation editor for an unpivot transformation with the **add dependent column** option.

To add a dependent column:

Choose add dependent column and select Unified Attributes from a dropdown menu.
Drag and drop the dependent column from the list at the left to its corresponding attribute on the right. In this example, the attribute containing unit information (VSTEMP_U) is the dependent column.

1181 — For VSTEMP, locate the attribute that provides the units, VSTEMP_U, and drag it to the corresponding Dependent data column space.

To see the following example of how you work with an unpivot with a dependent data column, choose Information .

581 — An example showing a patient's age and height, with separate units attributes for each one, being unpivoted into separate rows with the appropriate units value for each.

Note: You can also include an UNPIVOT statement in a transformation script. See UNPIVOT.