Unpivot

The unpivot operation works much the same as the SQL concept: "rotating" a set of column headers (and the associated values) into rows to create a more normalized table that's longer and narrower. It is nearly the opposite of the pivot operation, though it will not be able to unmerge records and restore nulls that were aggregated by the pivot operation. You can perform unpivot operations using /aggregate/unpivot or via the SQL UNPIVOT function. Visit the Aggregation documentation to review details on aggregating data.

An unpivot operation could be used to convert a table like this:

Name	Home Phone	Work Phone	Cell Phone
Jane	123-456-7890	111-222-3333
John	123-456-7890		333-222-1111

into a table like this:

Name	Phone Type	Phone Number
Jane	Home	123-456-7890
Jane	Work	111-222-3333
John	Home	123-456-7890
John	Cell	333-222-1111

Examples

Customer

Given a source table customer, which lists the name, ID, and home, work, & cell phone numbers for each customer in the table:

Name	ID	Home_Phone	Work_Phone	Cell_Phone
...	...	...	...	...

An unpivot operation can be performed in Python like so, creating separate home, work, & cell phone records for each customer without copying over their names:

gpudb.aggregate_unpivot(
    table_name = "customer",
    column_names = ["ID", "Home_Phone", "Work_Phone", "Cell_Phone"]
    variable_column_name = "phone_type",
    value_column_name = "phone_number",
    pivoted_columns = ["Home_Phone", "Work_Phone", "Cell_Phone"]
)

The resulting table looks like this

ID	phone_type	phone_number
...	...	...

Student

Given a source table student, which lists each student's quarterly grades as separate columns in a single record:

ID	q1_grade	q2_grade	q3_grade	q4_grade
...	...	...	...	...

Performing an unpivot operation in Python on the student table converts it into a table with separate records for each quarterly grade:

gpudb.aggregate_unpivot(
    table_name = "student",
    variable_column_name = "quarter",
    value_column_name = "grade",
    pivoted_columns = ["q1", "q2", "q3", "q4"]
)

The resulting table looks like this:

ID	quarter	grade
...	...	...

Limitations

• Store-only columns cannot have Unpivot operations applied to them, nor can they appear in the result set
• Non-charN string column types cannot appear in the result set if the result_table option is used
• The column types for the pivoted_columns must match, e.g., int8 to int8, char4 to char4, etc.