Electronic database operations for perspective transformations on relational tables using pivot and unpivot columns

A method of transforming data from a pivoted relational database table stored in an electronic data processor into an unpivoted relational table, both of the tables having an array of data values stored in cells of the table and organized as a plurality of rows and columns and having multiple column names associated with respective ones of the columns but not a part of the tables themselves, the method comprising:

• selecting a name as the name of a pivot column;
• selecting a plurality of names of the columns in the pivoted table as a pivot list;
• selecting a name as the name of a value column;
• accessing the pivoted table in the data processor;
• creating a pivot column in the unpivoted table, having a name stored outside the unpivoted column itself and selected as the pivot column name;
• converting the column names in the pivot list into data values stored within the unpivoted table in the pivot column;
• creating a value column in the unpivoted table having the name selected for the value column;
• for each particular data value in each particular column of the pivoted-table columns in the pivot list, placing the particular data value into the value column of the unpivoted table in a row which also contains a data value in the pivot column corresponding to the particular column of the pivoted table;
• and
• storing the unpivoted table in the data processor.

A relational database system, comprising:

• a number of clients;
• and
• a search engine including modules for parsing, optimizing, and executing a query from one of the clients containing a pivoting operation specifying a relational input table in a relational database, a name of a pivot column from outside the input table itself, and of a value column in the input table, and a pivot list of data values in the pivot column, the search engine transposing data items in the value column of the input table about the pivot column based upon the data items in the pivot list so as to construct a pivoted relational output table having the data items in the pivot list as column names outside the table itself,
• wherein the query from the one client includes an unpivot operation specifying an unpivoted table for inverting the effect of the pivot operation upon the pivoted table by transposing data items in the pivoted table about a number of pivoted columns.

A data-storage medium having a program stored thereon for causing a suitably programmed computer to perform the steps comprising:

• identifying a relational pivoted table, a pivot list of column names from outside the pivoted table, and a pivot-column name and a value-column name for an unpivoted table;
• constructing a pivot column and a value column in a relational unpivoted table;
• and
• transposing the pivoted table about the columns in the pivot list to place the names of the pivot-list columns as data items stored within the pivot column of the unpivoted table, and to place data items in the pivot-list columns into rows of the value column of the unpivoted table.