Unpivots one or more columns.
Information at a Glance
Category
|
Transform
|
Data source type
|
DB
|
Sends output to other operators
|
Yes
|
Data processing tool
|
SQL
|
Note: The Unpivot (DB) operator is for database data only. For Hadoop data, use the
Unpivot (HD) operator.
The columns selected are removed from the input and flattened into the following two new columns at the end of the output data set.
- The first column, whose values are the names of the chosen columns.
- The second column, whose values are the corresponding values in the chosen columns.
Input
A data set from a database.
Configuration
Parameter
|
Description
|
Notes
|
Any notes or helpful information about this operator's parameter settings. When you enter content in the
Notes field, a yellow asterisk is displayed on the operator.
|
Columns
|
The columns to unpivot. All data types are supported.
|
Name of Variable Column
|
The name of the first new column. This contains the names of the columns to unpivot.
Note: The value must be alphanumeric. (regular expression to match is : "^[A-Za-z]+ \\ w*$")
|
Name of Value Column
|
The name of the second new column. This contains the values of the columns to unpivot.
Note: The value must be alphanumeric. (regular expression to match is : "^[A-Za-z]+ \\ w*$")
|
Output Type
|
- TABLE outputs a database table. Specifying
TABLE enables
Storage Parameters.
- VIEW outputs a database view.
|
Output Schema
|
The schema for the output table or view.
|
Output Table
|
The table path and name where the results are output. By default, this is a unique table name based on your user ID, workflow ID, and operator.
|
Drop If Exists
|
Specifies whether to overwrite an existing table.
- Yes - If a table with the name exists, it is dropped before storing the results.
- No - If a table with the name exists, the results window shows an error message.
|
Output
If you select X columns to unpivot from an input with Y columns and N rows, the output data set has (Y-X+2) columns and (X * N) rows.
- Data Output
-
Note:
- The New Variable column contains the names of the unpivoted values in chararray format.
- For the New Value column:
- If all columns selected to unpivot are numeric, the resulting value column is double.
- If all columns selected to unpivot are datetime with the exact same format, the resulting value column is datetime with this same format.
- For all other cases, the resulting value column is chararray.
- All null values are kept in the output.
Example
Name
|
Mathematics
|
Science
|
English
|
John
|
90
|
70
|
50
|
Matt
|
60
|
40
|
80
|
After you select the Mathematics, Science, and English columns to unpivot, and specify new columns named Subject and Grade, the result is as follows:
Name
|
Subject
|
Grade
|
John
|
Mathematics
|
90
|
John
|
Science
|
70
|
John
|
English
|
50
|
Matt
|
Mathematics
|
60
|
Matt
|
Science
|
40
|
Matt
|
English
|
80
|
Copyright © Cloud Software Group, Inc. All rights reserved.