Databases Advanced

What is Crosstab Query?

A query that transforms rows into columns, creating a pivot table view of aggregated data.

Crosstab (pivot) queries rotate data from rows to columns. In PostgreSQL, the tablefunc extension provides the crosstab() function. This is useful for reporting — turning monthly sales rows into columns for each month.

Alternative approaches include CASE expressions with GROUP BY for manual pivoting. Crosstab queries simplify dashboard reporting, time-series analysis, and comparative data views.

Related Terms

Prepared Statement

A pre-compiled SQL template that uses parameters instead of literal values, preventing SQL injection and improving performance.

A data structure that improves the speed of data retrieval operations on database tables at the cost of additional storage.

A database object that enables row-by-row processing of query results, useful for operations that cannot be done in bulk.

Database Constraint

Rules enforced by the database to maintain data integrity, including NOT NULL, UNIQUE, CHECK, PRIMARY KEY, and FOREIGN KEY.

Connection Pool

A cache of database connections that can be reused, avoiding the overhead of creating new connections for each request.

PostgreSQL's binary JSON data type that stores JSON documents with indexing, querying, and manipulation capabilities.

View All Databases Terms →