A formal characterization of PIVOT/UNPIVOT

Authors:
Catharine M. Wyss;Edward L. Robertson
Affiliations:
Indiana University, Bloomington, IN;Indiana University, Bloomington, IN
Venue:
Proceedings of the 14th ACM international conference on Information and knowledge management
Year:
2005

Citing 7
Cited 6

SchemaSQL: An extension to SQL for multidatabase interoperability

ACM Transactions on Database Systems (TODS)
Database Management Systems

Database Management Systems
Computers and Intractability: A Guide to the Theory of NP-Completeness

Computers and Intractability: A Guide to the Theory of NP-Completeness
SchemaSQL - A Language for Interoperability in Relational Multi-Database Systems

VLDB '96 Proceedings of the 22th International Conference on Very Large Data Bases
Spreadsheets in RDBMS for OLAP

Proceedings of the 2003 ACM SIGMOD international conference on Management of data
Relational languages for metadata integration

ACM Transactions on Database Systems (TODS)
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

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

Proceedings of the 2007 ACM SIGMOD international conference on Management of data
Data exchange with data-metadata translations

Proceedings of the VLDB Endowment
Definition and Formalization of Entity Resolution Functions for Everyday Information Integration

Semantics in Data and Knowledge Bases
Updatable and evolvable transforms for virtual databases

Proceedings of the VLDB Endowment
Efficient resource attribute retrieval in RDF triple stores

Proceedings of the 20th ACM international conference on Information and knowledge management
Data mapping as search

EDBT'06 Proceedings of the 10th international conference on Advances in Database Technology

Quantified Score

Hi-index	0.00

Visualization

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.