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

• US 6,298,342 B1
• Filed: 03/16/1998
• Issued: 10/02/2001
• Est. Priority Date: 03/16/1998
• Status: Expired due to Fees

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Alger, Jeff, Graefe, Goetz
• Primary Examiner(s): Black, Thomas
• Assistant Examiner(s): RONES, CHARLES
• Application Number:
  US09/039,728
• Time in Patent Office: 1,296 Days
• Field of Search: 707/4, 707/10, 707/101, 707/104, 707/7, 707/2, 707/202
• US Class Current: 707/602
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99937 Sorting
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation

Abstract

A “pivot” operation rotates the data items in a relational database table so that certain data values in the table become column names of the pivoted table, and the data items of a specified value column appear in corresponding rows in the new columns of the pivoted table. A pivot list specifies that only certain values of the pivot column data items participate in the operation. Additional columns of the input table appear as columns in the output table; the rows of the output table are grouped by equal data-item values in these grouping columns. An “unpivot” operation provides the inverse of the pivot operation. Both operations may be nested in an SQL user query at the algebraic level. The operations occur in the search engine of a relational database management system, and may also be invoked as part of an optimization of another query.

Claims

1. A method of unpivoting data in a relational database management system on a digital computer, comprising:

identifying a relational pivoted table having data stored therein and having names associated therewith stored outside the pivoted table;

identifying a pivot-column name for a relational unpivoted table;

identifying a pivot list of columns of the pivoted table;

identifying a value-column name for the unpivoted table;

constructing a pivot column and a value column in the unpivoted table; and

transposing the pivoted table about the columns in the pivot list so as to place the names of the pivot-list columns from outside the input table as data items stored within the unpivoted table in the pivot column, and to place data items in the pivot-list columns into rows of the value column of the unpivoted table.

2. A method according to claim 1, comprising the further steps of:

identifying at least one grouping column of the pivoted table; and

grouping the rows of the unpivoted table according to equal values of the data items in the one or more grouping columns.

3. A method according to claim 1, wherein the transposing step occurs at a central server responding to users located multiple remote client locations, and wherein the identifying steps originate with one of the users.

4. A method according to claim 1, wherein the identifying steps originate in a search engine at the central server as a part of optimizing a query from a user which query does not include the pivot method as an explicit operation.

5. 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.

6. A method according to claim 5, wherein the pivoted table includes columns other than the columns in the pivot list, the method comprising the further step of grouping the rows of the unpivoted table by equal values of the data items in at least some of the other columns.

7. A method according to claim 6, wherein the grouping step groups the rows of the unpivoted table by equal values of the data items in all of the other columns.

8. 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.

9. A system according to claim 8, wherein the data items in the pivoted column are placed into the value column of the unpivoted table.

10. A relational database system, comprising:

a number of clients; and

a search engine including modules within itself 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, without exporting the data items or the pivot list.

11. A system according to claim 10, wherein the search engine reside in a central server, and the clients are physical located at multiple locations remote from the server.

12. A system according to claim 10, wherein the optimizer module optimizes the query including the pivot operation.

13. A storage medium for use in a suitably programmed digital computer, the medium containing computer-readable representations of an unpivoted relational table in a relational database, the unpivoted table having data values placed in rows and columns according to a pivot specification involving a pivoted relational input table, a pivot column, a pivot list, and a value column, the unpivoted table comprising:

a pivot column having a name corresponding to the pivot column, and whose rows contain data items stored within the pivoted input table itself and derived from the names of pivoted-table columns appearing in the pivot list, which names are separate from the pivoted table itself; and

a value column having a name derived from the name of a value column in the specification, each particular row of the value column containing a data item derived from the data item in that one of the pivoted columns whose name is a data item in the same particular row of the unpivoted table.

14. A table according to claim 13, wherein the pivoted table contains at least one further column in addition to the pivoted columns.

15. A table according to claim 14, wherein the rows of the unpivoted table are grouped by equal values of the at least one further column.

16. A table according to claim 13, wherein the table is stored on a storage medium.

17. 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.

18. A data-storage medium according to claim 17, wherein said programmed computer performs the further steps of:

identifying at least one grouping column of the pivoted table; and

grouping the rows of the unpivoted table according to equal values of the data items in the one or more grouping columns.