Article

A formal characterization of PIVOT/UNPIVOT

Authors:

Catharine M. Wyss,

Edward L. RobertsonAuthors Info & Claims

CIKM '05: Proceedings of the 14th ACM international conference on Information and knowledge management

Pages 602 - 608

https://doi.org/10.1145/1099554.1099709

Published: 31 October 2005 Publication History

Get Access

Abstract

PIVOT is an important relational operation that allows data in rows to be exchanged for columns. Although most current relational database management systems support PIVOT-type operations, to date a purely formal, algebraic characterization of PIVOT has been lacking. In this paper, we present a characterization in terms of extended relational algebra operators τ (transpose), Π (drop projection), and μ (unique optimal tuple merge). This enables us to (1) draw parallels with PIVOT and existing operators employed in Dynamic Data Mapping Systems (DDMS), (2) formally characterize invertible PIVOT instances, and (3) provide complexity results for PIVOT-type operations. These contributions are an important part of ongoing work on formal models for relational OLAP.

References

[1]

Fulya Erdinc and Catharine M. Wyss. Intrinsic, Rule-Based Optimization of PIVOT and UNPIVOT Operations within RDBMS. Forthcoming.

Google Scholar

[2]

Conor Cunningham, César A. Galindo-Legaria, and Goetz Graefe. PIVOT and UNPIVOT: Optimization and Execution Strategies in an RDBMS. VLDB 2004.

Digital Library

Google Scholar

[3]

M. R. Garey and D. S. Johnson. Computers and Intractability: A Guide to the Theory of NP-Completeness. W. H. Freeman and Company, 1979.

Digital Library

Google Scholar

[4]

L. V. S. Lakshmanan, F. Sadri, and I. N. Subramanian. SchemaSQL - A Language for Interoperability in Relational Multi-Database Systems. VLDB 1996.

Digital Library

Google Scholar

[5]

L. V. S. Lakshmanan, F. Sadri, and I. N. Subramanian. SchemaSQL - An Extension to SQL for Multidatabase Interoperability. TODS, December 2001.

Digital Library

Google Scholar

[6]

Raghu Ramakrishnan and Johannes Gehrke. Database Management Systems, 3rd Edition. McGraw-Hill, 2003.

Digital Library

Google Scholar

[7]

Edward L. Robertson and Catharine M. Wyss. Optimal Tuple Merge is NP-complete. IUCS Technical Report TR599, August 2004.

Google Scholar

[8]

Catharine M. Wyss and Edward L. Robertson. Relational Languages for Metadata Integration. TODS, June 2005.

Digital Library

Google Scholar

[9]

Andrew Witkowski, Srikanth Bellamkonda, Tolga Bozkaya, Gregory Dorman, Nathan Folkert, Abhinav Gupta, Lei Shen, and Sankar Subramanian. Spreadsheets in RDBMS for OLAP. SIGMOD 2003.

Digital Library

Google Scholar

Cited By

View all

Abgrall T(2022)Formalization of Data Integration TransformationsNew Trends in Database and Information Systems10.1007/978-3-031-15743-1_56(615-622)Online publication date: 29-Aug-2022
https://doi.org/10.1007/978-3-031-15743-1_56
Petersohn DMacke SXin DMa WLee DMo XGonzalez JHellerstein JJoseph AParameswaran A(2020)Towards scalable dataframe systemsProceedings of the VLDB Endowment10.14778/3407790.340780713:12(2033-2046)Online publication date: 1-Jul-2020
https://dl.acm.org/doi/10.14778/3407790.3407807
Britell SDelcambre LAtzeni P(2017)Web System Development Using Polymorphic Widgets and Generic SchemasConceptual Modeling Perspectives10.1007/978-3-319-67271-7_9(121-135)Online publication date: 13-Oct-2017
https://doi.org/10.1007/978-3-319-67271-7_9
Show More Cited By

Index Terms

A formal characterization of PIVOT/UNPIVOT
1. Information systems
  1. Data management systems
    1. Database design and models
    2. Query languages
2. Theory of computation
  1. Randomness, geometry and discrete structures

Recommendations

PIVOT and UNPIVOT: optimization and execution strategies in an RDBMS
VLDB '04: Proceedings of the Thirtieth international conference on Very large data bases - Volume 30

PIVOT and UNPIVOT, two operators on tabular data that exchange rows and columns, enable data transformations useful in data modeling, data analysis, and data presentation. They can quite easily be implemented inside a query processor, much like select, ...
Extending relational query optimization to dynamic schemas for information integration in multidatabases
SIGMOD '07: Proceedings of the 2007 ACM SIGMOD international conference on Management of data

This paper extends relational processing and optimization to the FISQL/FIRA languages for dynamic schema queries over multidatabases. Dynamic schema queries involve the creation and restructuring of metadata at runtime. We present a full implementation ...
Efficient Storage and Querying of Horizontal Tables Using a PIVOT Operation in Commercial Relational DBMSs

In recent years, a horizontal table with a large number of attributes is widely used in OLAP or e-business applications to analyze multidimensional data efficiently. For efficient storing and querying of horizontal tables, recent works have tried to ...

Comments

Information & Contributors

Information

Published In

CIKM '05: Proceedings of the 14th ACM international conference on Information and knowledge management

October 2005

854 pages

ISBN:1595931406

DOI:10.1145/1099554

General Chair:
Otthein Herzog
University of Bremen, Germany
,
Program Chairs:
Hans-Jörg Schek
University for Health Sciences, Medical Informatics and Technology, Austria
,
Norbert Fuhr
University of Duisburg-Essen, Germany
,
Abdur Chowdhury
America Online, USA
,
Wilfried Teiken
IBM T.J. Watson Research Center, USA

Permission to make digital or hard copies of all or part of this work for personal or classroom use is granted without fee provided that copies are not made or distributed for profit or commercial advantage and that copies bear this notice and the full citation on the first page. Copyrights for components of this work owned by others than ACM must be honored. Abstracting with credit is permitted. To copy otherwise, or republish, to post on servers or to redistribute to lists, requires prior specific permission and/or a fee. Request permissions from [email protected]

Publisher

Association for Computing Machinery

New York, NY, United States

Publication History

Published: 31 October 2005

Permissions

Request permissions for this article.

Request Permissions

Check for updates

Author Tags

Qualifiers

Article

Conference

CIKM05

Sponsor:

CIKM05: Conference on Information and Knowledge Management

October 31 - November 5, 2005

Bremen, Germany

Acceptance Rates

CIKM '05 Paper Acceptance Rate 77 of 425 submissions, 18%;

Overall Acceptance Rate 1,861 of 8,427 submissions, 22%

Other Metrics

View Article Metrics

Bibliometrics & Citations

Bibliometrics

Article Metrics

12
Total Citations
View Citations
488
Total Downloads

Downloads (Last 12 months)8
Downloads (Last 6 weeks)1

Reflects downloads up to 05 Mar 2025

Other Metrics

View Author Metrics

Citations

Cited By

View all

Abgrall T(2022)Formalization of Data Integration TransformationsNew Trends in Database and Information Systems10.1007/978-3-031-15743-1_56(615-622)Online publication date: 29-Aug-2022
https://doi.org/10.1007/978-3-031-15743-1_56
Petersohn DMacke SXin DMa WLee DMo XGonzalez JHellerstein JJoseph AParameswaran A(2020)Towards scalable dataframe systemsProceedings of the VLDB Endowment10.14778/3407790.340780713:12(2033-2046)Online publication date: 1-Jul-2020
https://dl.acm.org/doi/10.14778/3407790.3407807
Britell SDelcambre LAtzeni P(2017)Web System Development Using Polymorphic Widgets and Generic SchemasConceptual Modeling Perspectives10.1007/978-3-319-67271-7_9(121-135)Online publication date: 13-Oct-2017
https://doi.org/10.1007/978-3-319-67271-7_9
Britell SDelcambre LAtzeni P(2016)Facilitating Data-Metadata Transformation by Domain Specialists in a Web-Based Information System Using Simple CorrespondencesConceptual Modeling10.1007/978-3-319-46397-1_34(445-459)Online publication date: 7-Oct-2016
https://doi.org/10.1007/978-3-319-46397-1_34
Terwilliger J(2013)Bidirectional by Necessity: Data Persistence and Adaptability for Evolving Application DevelopmentGenerative and Transformational Techniques in Software Engineering IV10.1007/978-3-642-35992-7_6(219-270)Online publication date: 2013
https://doi.org/10.1007/978-3-642-35992-7_6
Brodt ASchiller OMitschang B(2011)Efficient resource attribute retrieval in RDF triple storesProceedings of the 20th ACM international conference on Information and knowledge management10.1145/2063576.2063785(1445-1454)Online publication date: 24-Oct-2011
https://dl.acm.org/doi/10.1145/2063576.2063785
Terwilliger JDelcambre LMaier DSteinhauer JBritell S(2010)Updatable and evolvable transforms for virtual databasesProceedings of the VLDB Endowment10.14778/1920841.19208833:1-2(309-319)Online publication date: 1-Sep-2010
https://dl.acm.org/doi/10.14778/1920841.1920883
Fletcher GWyss C(2009)Towards a General Framework for Effective Solutions to the Data Mapping ProblemJournal on Data Semantics XIV10.1007/978-3-642-10562-3_2(37-73)Online publication date: 15-Nov-2009
https://dl.acm.org/doi/10.1007/978-3-642-10562-3_2
Hernández MPapotti PTan W(2008)Data exchange with data-metadata translationsProceedings of the VLDB Endowment10.14778/1453856.14538881:1(260-273)Online publication date: 1-Aug-2008
https://dl.acm.org/doi/10.14778/1453856.1453888
Archer DDelcambre L(2008)Definition and Formalization of Entity Resolution Functions for Everyday Information IntegrationSemantics in Data and Knowledge Bases10.1007/978-3-540-88594-8_7(126-142)Online publication date: 21-Oct-2008
https://dl.acm.org/doi/10.1007/978-3-540-88594-8_7
Show More Cited By

View Options

Login options

Check if you have access through your login credentials or your institution to get full access on this article.

Full Access

Get this Publication

View options

PDF

View or Download as a PDF file.

PDF

eReader

View online with eReader.

eReader

Abstract

References

Cited By

Index Terms

Recommendations

PIVOT and UNPIVOT: optimization and execution strategies in an RDBMS

Extending relational query optimization to dynamic schemas for information integration in multidatabases

Efficient Storage and Querying of Horizontal Tables Using a PIVOT Operation in Commercial Relational DBMSs

Comments

Information

Published In

Sponsors

Publisher

Publication History

Permissions

Check for updates

Author Tags

Qualifiers

Conference

Acceptance Rates

Contributors

Other Metrics

Bibliometrics

Article Metrics

Other Metrics

Citations

Cited By

Login options

Full Access

View options

PDF

eReader

Share

Share this Publication link

Share on social media

Affiliations