Version:

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.

Unpivot Memory-Only Tables

  • Creating a memory-only table results in an entirely new data set, so be mindful of the memory usage implications.
  • An unpivot memory-only table cannot be created with a non-charN string column type.
  • If an unpivot memory-only table is created and its source data set's rows are updated (or removed), the memory-only table will not be updated to reflect the changes in the source data set.
  • An unpivot memory-only table cannot be created from a collection.
  • An unpivot memory-only table is transient, by default, as it is not protected and will expire after the default TTL setting.
  • An unpivot memory-only table is not persisted, by default, and will not survive a database restart; specifying a result_table_persist option of true will make the table permanent and not expire.