UNPIVOT reorganizes the structure of the 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 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 each quarter, Q1, Q2, Q3, and Q4. You can use the Unpivot option to reorganize this table and change these four attributes into two: one attribute for the Quarter in which the spend occurred, and another attribute for the amount of Spend.
In this example, we create an attribute named Quarter and populate it with the name of the Q1, Q2, Q3, and Q4 attributes. The Spend attribute is then populated by values from the corresponding quarters.
The result is that Quarter and Spend are filled with the information from the original dataset. The columns 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 in the dataset is four times larger than the number of records in each of the input datasets.
When you need to unpivot multiple attributes together, you can add dependent columns. One common use case is when the variable attribute contains data that have 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 our example, the attribute containing unit information (VSTEMP_U) is the dependent column.
Here is an example of how it works. To see it, choose the information icon:
Note: You can also include an
UNPIVOT statement in a transformation script. See UNPIVOT.
Updated 3 months ago