How to Transform SQL Queries to Crosstabs in PostgreSQL
Tags: postgres, crosstab, sql, database
Published: 2023-03-08 18:59:34

---
title: "How to Transform SQL Queries to Crosstabs in PostgreSQL"
published: true
description: "Learn how to transform SQL queries into crosstabs in PostgreSQL using CASE conditional expressions, aggregate FILTER clauses, or the PostgreSQL crosstab() function." 
tags: ["postgres", "crosstab", "sql", "database"]
cover_image: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kpafajxukbz17n8zr2nv.jpg
canonical_url: https://dev.to/kagundajm/how-to-transform-sql-queries-to-crosstabs-in-postgresql-1ma6 
# Use a ratio of 100:42 for best results.
# published_at: 2023-03-08 14:39 +0000
---

A SELECT SQL query retrieves data from a database in a tabular form. The first row, or header row, has the column names, and all the other rows have the data that was retrieved.

![Listing of salesmen monthly sales data](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ted00nndri7ivfz0g1d8.png)

In a crosstab, the data is condensed and the names of one or more columns are rotated. You can add row and column totals to a crosstab. For example, a list of salesmen's monthly sales may include the months in the column headers. By rotating the data, it is easier to read and understand how the facts relate to one another.

![Sample crosstab](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/s31qo6xzt67yr7c98a48.png)

Other names you might come across for crosstabs are matrix reports, pivot for SQL server databases, and pivot tables on spreadsheets like Microsoft Excel, Google Sheets, and LibreOffice.

The following topics are covered in this post:
- [Crosstabs using CASE conditional expression](#crosstabs-using-case-conditional-expression)
- [Using Common Table Expressions (CTE) for a crosstab](#using-common-table-expressions-cte-for-a-crosstab)
- [Crosstabs using aggregate FILTER clause](#crosstabs-using-aggregate-filter-clause)
- [Using the `crosstab()` function in PostgreSQL](#using-postgresql-crosstab-function)
- [Using a PostgreSQL `crosstab()` function with more than three columns](#using-a-postgresql-crosstab-function-with-more-than-three-columns)
- [Using ARRAY data type to re-arrange extra crosstab columns](#using-array-data-type-to-re-arrange-extra-crosstab-columns)

This post uses queries from a database populated with data from the [Northwind database for Postgres](https://github.com/pthom/northwind_psql).  The queries are run using [DBeaver](https://dbeaver.io/) SQL client.

## Crosstabs Using CASE Conditional Expression <a name="crosstabs-using-case-conditional-expression"></a>

The SQL CASE expression enables you to choose a value depending on a condition, much like an **if-then-else** conditional statement. The syntax for the CASE expression is as follows:

```sql
CASE WHEN condition THEN result
     [WHEN ...]
     [ELSE result]
END
```

If a `condition` evaluates to `true`, the `result` value will be chosen, otherwise; the `result` value contained in the optional **ELSE** expression will be chosen.

In the example below, the SQL CASE statement is used to make a crosstab of monthly total sales by employees for the first four months of 1997.


{% gist https://gist.github.com/kagundajm/5fe5ada4cab96534a38f4c42aaef3f11 file=CASE-Conditional-Expression-CTE.sql %}

If you run the query above, you will get a crosstab like the one below.

![employees monthly total sales crosstab during the first four months of 1997](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5yrk210yfxsk7d1yqdm7.png)

You can combine the previous query with a **UNION ALL** operator and the following **SQL SELECT** query to include the total for each month in the cross-tab.

{% gist https://gist.github.com/kagundajm/5fe5ada4cab96534a38f4c42aaef3f11 file=CASE-Conditional-Expression-Union-All.sql %}

![employees monthly total sales crosstab during the first four months 1997 including month totals using SQL CASE expression](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yy8d35ql3j8qnv5lcels.png)

The SQL CASE expression is supported by most database systems.

## Using Common Table Expressions (CTE) For A Crosstab <a name="using-common-table-expressions-cte-for-a-crosstab"></a>

Common Table Expressions (CTE) in SQL let you create a temporary table in a SELECT SQL statement. It is then possible to reference this named temporary table in subsequent SELECT, INSERT, UPDATE, or DELETE SQL statements. The CTEs simplify the SQL queries by breaking them down into smaller, more manageable parts that are easier to read and understand. In PostgreSQL, CTEs are called [WITH queries](https://www.postgresql.org/docs/current/queries-with.html).

You can rewrite the previous CASE crosstab query using a CTE as follows:

{% gist https://gist.github.com/kagundajm/5fe5ada4cab96534a38f4c42aaef3f11 file=CASE-Conditional-Expression-CTE.sql  %}

## Crosstabs Using Aggregate FILTER Clause <a name="crosstabs-using-aggregate-filter-clause"></a>

From PostgreSQL 9.4, you can use the [FILTER clause](https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-AGGREGATES) to perform  [aggregate functions](https://www.postgresql.org/docs/15/tutorial-agg.html) on specific records. The FILTER clause is less wordy and has a cleaner syntax than the CASE statement.

The following SQL query uses the FILTER clause to generate a crosstab.

{% gist https://gist.github.com/kagundajm/5fe5ada4cab96534a38f4c42aaef3f11 file=Crosstabs-FILTER-clause.sql %}

The SQL **COALESCE** function replaces any null values in the crosstab with zero (0) values.

![employees monthly total sales crosstab during the first four months of 1997 including month totals using aggregate FILTER clause](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tjbqz51iux46skpx44rg.png)

## Using PostgreSQL `crosstab()` Function <a name="using-postgresql-crosstab-function" ></a>

The `crosstab()` function is part of the optional [`tablefunc`](https://www.postgresql.org/docs/current/tablefunc.html) module.

You can run the `SELECT COUNT(*) FROM pg_extension WHERE extname='tablefunc';` query to see if the `tablefunc` extension is installed on the database you are using. If the result of the query is 0, install and activate the `tablefunc⁣` extension using the `CREATE EXTENSION IF NOT EXIST tablefunc;⁣` SQL command. The `tablefunc⁣` module can be installed by non-superusers who have the `CREATE⁣` privilege.

The `crosstab` function has several options, `crosstab(sql text)`, `crosstabN(sql text)`, and `crosstab(source_sql text, category_sql text)`. Examples in this post use the `crosstab(source_sql text, category_sql text)` function option.

The first parameter (`source_sql text`) in a `crosstab(source_sql text, category_sql text)` function is the source SQL **SELECT** query statement and must return at least three (3) columns of data to pivot or rotate. The first column (`row_name`) contains data values to be used as row identifiers in the final result; data in the second column (`category`) represents category values that will be rotated to column headers in the pivot table, and the third column (`value`) contains data to be assigned to each cell of the final crosstab. The second parameter `text category_sql` is a query returning a category list for the columns.

The crosstab function returns a set of records with unknown data types. Therefore, you must alias the returned columns with column names and types using the `AS (col1 type, col2 type, ...)` clause. Failing to alias the columns will cause **a column definition list is required for functions returning "record"** error.

The SQL query below shows how to use the `crosstab(source_sql text, category_sql text)` function. A dollar-quoted string constant (`$$`) has been used to maintain formatting consistency and remove the need to escape single quotes (') by doubling them.

{% gist https://gist.github.com/kagundajm/5fe5ada4cab96534a38f4c42aaef3f11 file=postgreSQL-crosstab-function.sql %}

![employees monthly total sales crosstab during the first four months of 1997 using the crosstab() function](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fbn1oy8u9gr4ha72enc4.png)

## Using A PostgreSQL `crosstab()` Function With More Than Three Columns <a name="using-a-postgresql-crosstab-function-with-more-than-three-columns"></a>

If your `source_sql text` SQL query returns more than three columns, the additional or extra columns must be placed between the  `row_name` and `category` columns. In the previous crosstab SQL query, any extra or additional columns must be placed between the `salesman` and `order_month` columns.

 PostgreSQL [converts identifiers/column names to lowercase](https://www.postgresql.org/docs/current/sql-syntax-lexical.html) by default. To capitalize the crosstab column headers, enclose them within double quotes.

{% gist https://gist.github.com/kagundajm/5fe5ada4cab96534a38f4c42aaef3f11 file=postgreSQL-crosstab-more-than-3-columns.sql %}

![employees monthly total sales crosstab during the first four months of 1997 using crosstab() function with more than 3 columns](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9kqrf9t96ms4zjh61tqj.png)

## Using ARRAY Data Type To Re-Arrange Extra Crosstab Columns <a name="using-array-data-type-to-re-arrange-extra-crosstab-columns"></a>
A crosstab with more than three columns has limitations in the order placement of the additional columns in the final crosstab. However, you can re-arrange the crosstab columns by inserting the extra columns into an [arrays](https://www.postgresql.org/docs/current/arrays.html) data type.

{% gist https://gist.github.com/kagundajm/5fe5ada4cab96534a38f4c42aaef3f11 file=postgreSQL-crosstab-rearrange-extra-columns.sql %}

![employees monthly total sales crosstab during the first four months 1997 using crosstab() function with re-arranged columns](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/53dyjty9dxaj14jesysn.png)

## Conclusion
Crosstabs are a powerful way to summarize and analyze data from a database. By presenting data in a condensed and organized format, crosstabs make it easier to analyze relationships between different variables.

The CASE conditional expression, aggregate FILTER clause, and the PostgreSQL crosstab() function are some of the methods for creating crosstabs. If the crosstab() function uses more than three columns to summarize data, the ARRAY data type may be used to re-arrange the extra columns into the correct positions. Using crosstabs, PostgreSQL users can create reports and dashboards that help them make informed business decisions.


## Resources

1. [CrossTab Queries in PostgreSQL using tablefunc contrib](https://www.postgresonline.com/article_pfriendly/14.html)
1. [Static and dynamic pivots](https://postgresql.verite.pro/blog/2018/06/19/crosstab-pivot.html)