Method, system, program, and data structure for pivoting columns in a database table

• US 6,604,095 B1
• Filed: 09/21/1999
• Issued: 08/05/2003
• Est. Priority Date: 09/21/1999
• Status: Expired due to Term

• Current Assignee: Google LLC (Alphabet Inc.)
• Original Assignee: International Business Machines Corporation
• Inventors: Mandel, Richard Henry III, Cesare, Mark Anthony, Jerves, Julie Ann
• Primary Examiner(s): Alam, Hosain T.
• Assistant Examiner(s): FLEURANTIN, JEAN B
• Application Number:
  US09/400,057
• Time in Patent Office: 1,414 Days
• Field of Search: 707/1, 707/4, 707/10, 707/104.1
• US Class Current: 1/1
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: Y10S 707/99931 Database or file accessing
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99945 Object-oriented database structure processing

Abstract

Disclosed is a method, system, program, and data structure for transforming at least one column in a database input table. An input data table has multiple columns and rows. A determination is made of multiple data column names and multiple pivot columns in the input table. A row is generated in an output table for each data column name, having one data column name field including one data column name and at least one pivot column field including the value in one pivot column field in the input table, wherein each of the rows in the output table for each data column name has different values in at least one of the data column name field and pivot column field.

Claims

1. A method for transforming at least two columns in an input table, comprising:

determining an input data table comprised of multiple columns and rows;

determining multiple data column names;

determining multiple pivot columns in the input table; and

generating a row in an output table for each data column name and for each pivot column value in each pivot column, having one data column name field including one data column name and at least one pivot column field including the value in one pivot column in the input table, wherein each of the rows in the output table for each data column name has different values in at least one of the data column name field and pivot column field.

2. The method of claim 1, wherein each pivot column field in one row in the input table is copied to one pivot column field in one row in the output table, thereby transferring fields in multiple pivot columns in the input table to one pivot column in multiple rows in the output table.

3. The method of claim 2, wherein one row for each data column name and pivot column field is generated in the output table for each row in the input table.

4. The method of claim 2, further comprising determining at least one carry over column in the input table, wherein the rows in the input table have different values in at least one carry over column, and wherein one row for each data column name is generated in the output table for each row in the input table such that the rows generated for one row in the input table have the same values in the at least one carry over column field and different values in the data column name field and the at least one pivot column field.

5. The method of claim 1, wherein for each pivot column field in the input table there is one row in the output table having one data column name field and pivot column field including the pivot column name and corresponding pivot column value in the input table, respectively.

6. The method of claim 1, wherein determining multiple pivot columns in the input table comprises determining at least one pivot group including at least one pivot column in the input table, wherein generating the at least one pivot column field in the output table row comprises generating a pivot group column in the output table row for each pivot group, wherein each pivot group column in the output table includes a value in one of the pivot column fields in the pivot group in the input table, thereby aggregating the input table pivot columns into one pivot group in one pivot group column in the output table.

7. The method of claim 6, further comprising determining at least one carry over column in the input table, wherein each row in the input table has a different value in at least one carry over column, and wherein one row is generated in the output table for each possible data column name and input table row combination, such that the rows generated for one row in the input table have the same values in the at least one carry over column field and different values in the data column name field and the at least one pivot group column field.

8. The method of claim 6, wherein pivot columns in one pivot group in the input table are of the same data type.

9. The method of claim 6, wherein the number of data column names and pivot columns in each pivot group is the same.

10. A system for transforming at least two columns in an input table, comprising:

means for determining an input data table comprised of multiple columns and rows;

means for determining multiple data column names;

means for determining multiple pivot columns in the input table; and

means for generating a row in an output table for each data column name and for each pivot column value in each pivot column, having one data column name field including one data column name and at least one pivot column field including the value in one pivot column in the input table, wherein each of the rows in the output table for each data column name has different values in at least one of the data column name field and pivot column field.

11. The system of claim 10, wherein each pivot column field in one row in the input table is copied to one pivot column field in one row in the output table, thereby transferring fields in multiple pivot columns in the input table to one pivot column in multiple rows in the output table.

12. The system of claim 11, wherein one row for each data column name and pivot column field is generated in the output table for each row in the input table.

13. The system of claim 11, further comprising means for determining at least one carry over column in the input table, wherein the rows in the input table have different values in at least one carry over column, and wherein one row for each data column name is generated in the output table for each row in the input table such that the rows generated for one row in the input table have the same values in the at least one carry over column field and different values in the data column name field and the at least one pivot column field.

14. The system of claim 10, wherein for each pivot column field in the input table there is one row in the output table having one data column name field and pivot column field including the pivot column name and corresponding pivot column value in the input table, respectively.

15. The system of claim 10, wherein determining multiple pivot columns in the input table comprises determining at least one pivot group including at least one pivot column in the input table, wherein generating the at least one pivot column field in the output table row comprises generating a pivot group column in the output table row for each pivot group, wherein each pivot group column in the output table includes a value in one of the pivot column fields in the pivot group in the input table, thereby aggregating the input table pivot columns into one pivot group in one pivot group column in the output table.

16. The system of claim 15, further comprising means for determining at least one carry over column in the input table, wherein each row in the input table has a different value in at least one carry over column, and wherein one row is generated in the output table for each possible data column name and input table row combination, such that the rows generated for one row in the input table have the same values in the at least one carry over column field and different values in the data column name field and the at least one pivot group column field.

17. The system of claim 16, wherein pivot columns in one pivot group in the input table are of the same data type.

18. The system of claim 15, wherein the number of data column names and pivot columns in each pivot group is the same.

19. An article of manufacture for use in transforming at least two columns in an input table, the article of manufacture comprising computer usable media including at least one computer program embedded therein that causes the computer to perform:

determining an input data table comprised of multiple columns and rows;

determining multiple data column names;

determining multiple pivot columns in the input table; and

generating a row in an output table for each data column name and for each pivot column value in each pivot column, having one data column name field including one data column name and at least one pivot column field including the value in one pivot column in the input table, wherein each of the rows in the output table for each data column name has different values in at least one of the data column name field and pivot column field.

20. The article of manufacture of claim 19, wherein each pivot column field in one row in the input table is copied to one pivot column field in one row in the output table, thereby transferring fields in multiple pivot columns in the input table to one pivot column in multiple rows in the output table.

21. The article of manufacture of claim 20, wherein one row for data column name and pivot column field is generated in the output table for each row in the input table.

22. The article of manufacture of claim 20, further comprising determining at least one carry over column in the input table, wherein the rows in the input table have different values in at least one carry over column, and wherein one row for each data column name is generated in the output table for each row in the input table such that the rows generated for one row in the input table have the same values in the at least one carry over column field and different values in the data column name field and the at least one pivot column field.

23. The article of manufacture of claim 19, wherein for each pivot column field in the input table there is one row in the output table having one data column name field and pivot column field including the pivot column name and corresponding pivot column value in the input table, respectively.

24. The article of manufacture of claim 19, wherein determining multiple pivot columns in the input table comprises determining at least one pivot group including at least one pivot column in the input table, wherein generating the at least one pivot column field in the output table row comprises generating a pivot group column in the output table row for each pivot group, wherein each pivot group column in the output table includes a value in one of the pivot column fields in the pivot group in the input table, thereby aggregating the input table pivot columns into one pivot group in one pivot group column in the output table.

25. The article of manufacture of claim 24, further comprising determining at least one carry over column in the input table, wherein each row in the input table has a different value in at least one carry over column, and wherein one row is generated in the output table for each possible data column name and input table row combination, such that the rows generated for one row in the input table have the same values in the at least one carry over column field and different values in the data column name field and the at least one pivot group column field.

26. The article of manufacture of claim 24, wherein pivot columns in one pivot group in the input table are of the same data type.

27. The article of manufacture of claim 24, wherein the number of data column names and pivot columns in each pivot group is the same.

28. A memory device including a command for performing a pivot operation on a computer database input table, the command comprising

an input data table name parameter indicating the input table comprised of multiple columns and rows subject to the pivot operation;

and

a data column parameter indicating multiple data column names;

a pivot column parameter indicating multiple pivot columns in the input table, wherein a row is generated in an output table for each data column name and for each pivot column value in each pivot column, having one data column name field including one data column name and at least one pivot column field including the value in at least one of the pivot column in the input table.

29. The memory of claim 28, further comprising determining a carry over column parameter indicating at least one carry over column in the input table, wherein the rows in the input table have different values in at least one carry over column, and wherein one row for each data column name is generated in the output table for each row in the input table such that the rows generated for one row in the input table have the same values in the at least one carry over column field and different values in the data column name field and the at least one pivot column field.

30. The memory of claim 29, wherein the pivot column parameter indicates at least one pivot group including at least one pivot column in the input table, wherein generating the at least one pivot column field in the output table row comprises generating a pivot group column in the output table row for each pivot group, wherein each pivot group column in the output table includes a value in one of the pivot column fields in the pivot group in the input table, thereby aggregating the input table pivot columns into one pivot group in one pivot group column in the output table.

31. The memory device method of claim 30, wherein pivot columns in one pivot group in the input table are of the same data type.

32. The memory device of claim 30, wherein the number of data column names and pivot columns in each pivot group is the same.