Unpivot

The syntax for UNPIVOT is:

UNPIVOT 
(inputCol1A AS outputColA, inputCol1B AS outputColB)
(inputCol2A AS outputColA, inputCol2B AS outputColB)

where 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, outputColA and outputColB. It adds values from the input table's columns A and B into the new columns.

The following statements describe the behavior of UNPIVOT.

• UNPIVOT creates new columns. If no value is defined for a column, then it is null.
• UNPIVOT carries 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 UNPIVOT definition. The Name column 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 UNPIVOT statement can result in an unintentional change to tamr_id values in the unified dataset. To avoid data loss and maintain the stability of tamr_id in mastering and categorization projects, as a best practice Tamr recommends that you explicitly set the tamr_id to a value you're sure is stable in the last transformation. See Managing Primary Keys.

Example 1

In this example, UNPIVOT reorganizes rows that describe individual persons into rows of features (Height and Age), where each row includes information for a specific person, for that feature.

The initial input table looks as follows:

This statement:

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, Feature and 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.

Example 2

In this example, the following input table exists:

This statement:

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|