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.
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.
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.
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.
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.
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.
To see the following example of how you work with an unpivot with a dependent data column, choose Information .
Note: You can also include an
UNPIVOT statement in a transformation script. See UNPIVOT.
Updated over 1 year ago