UNPIVOT allows you to reorganize the structure of the input table. It takes related columns of data and moves them into different rows.
The syntax for
UNPIVOT (inputCol1A AS outputColA, inputCol1B AS outputColB) (inputCol2A AS outputColA, inputCol2B AS outputColB)
inputCol1A is a column in the input table, and
outputColA is a column in the output table.
This syntax shows a statement that takes two new columns in the output table,
outputColB. It adds values from the input table's columns A and B into the new columns.
The following statements describe the behavior of
UNPIVOTcreates new columns. If no value is defined for a column, then it is null.
UNPIVOTcarries over all columns that are not included in its definition. Specifically, it carries over all columns except columns explicitly included in an expression and new columns created by the expressions. To carry over specific columns, such as table keys, include them explicitly in the
Namecolumn in the following Example 1 illustrates this. To avoid overwriting existing columns if you want to carry them over to the new table, create new columns with different names than existing columns in the input table.
Important: Changing an
UNPIVOTstatement can result in an unintentional change to
tamr_idvalues in the unified dataset. To avoid data loss and maintain the stability of
tamr_idin mastering and categorization projects, as a best practice Tamr recommends that you explicitly set the
tamr_idto a value you're sure is stable in the last transformation. See Managing Primary Keys.
In this example,
UNPIVOT reorganizes rows that describe individual persons into rows of features (
Age), where each row includes information for a specific person, for that feature.
The initial input table looks as follows:
UNPIVOT ( Name, 'Height (in)' AS Feature, "Height (in)" AS Value ), ( Name, 'Age' AS Feature, Age AS Value )
creates the following output table with two new columns,
Value and retains the column
Name that was present in the original table:
Notice that the
Height (in) and
Age columns were not carried over to the output table because they were included in the expressions.
In this example, the following input table exists:
|Name||Height||Height Unit||Age||Weight (lbs)|
UNPIVOT ( Name, 'Height' AS Feature, Height AS Value, "Height Unit" AS Unit ), ( Name, 'Age' AS Feature, Age AS Value ), ( Name, 'Weight' AS Feature, Weight AS Value, 'lbs' AS Unit ) ``` creates the following output table with the new columns `Feature`, `Value`, and `Unit`. It carries over the column `Bookkeeping ref`: |Name |Feature|Value|Unit| Bookkeeping ref| |---|--|---|---| |Alice| Height | 66 | in | AC94-78FB | |Alice| Age | 28 | | AC94-78FB| |Alice| Weight | 132 | lbs | AC94-78FB| |Bob | Height | 176 | cm | 09A3-7582| |Bob | Age | 22 | | 09A3-7582| |Bob | Weight | 132 | lbs | 09A3-7582|
Updated 10 months ago