In PostgreSQL, you can rotate a table using the CROSSTAB function. This function is passed a SQL query as a text parameter, which returns three columns:
We’ll explain this using the Painting database as an example.
Let’s sum up the amount of paint of each color for each square:
Here, we confined ourselves to squares with IDs in the range between 12 to 16 in order to, on the one hand, keep the output small, yet, on the other hand, retain its representativeness. The colors are sorted in RGB order. Here is the result set:
In CROSSTAB terms, the spray can numbers represent row IDs, and the colors correspond to categories. The result of the table rotation should be as follows:
Now, let’s try to write a query using CROSSTAB that will return the desired result:
Here, we have to list all columns specifying their types. Yet some of the category columns may be omitted. Let’s look at the result (you can check your queries using the console, and setting PostgreSQL for execution):
This result isn’t exactly what we expected. We recall that only the order of the categories matters. If the square has been painted with one color only, the value (total amount of paint) lands in the first category (named R in our query) no matter what color it actually was. Let’s rewrite our query to make it return values for all colors, and that in the order needed. Missing colors should be replaced by NULL. To achieve this, we’ll add for each square and color a row with a NULL in the paint amount field:
Now the query below will return the required result.
You probably already start asking yourself whether there is some easier way to do that.
The answer is “yes”. As it turns out CROSSTAB can be passed another optional parameter – a query returning the category list in the same order used for the columns. Then, we can modify our first query as follows to make it yield the correct result:
Since PostgreSQL allows the usage of table value constructors, the query
If, when running the query, you get an error message that crosstab is not recognized as a function, it means you don’t have the tablefunc module installed. This can be fixed by a simple command (starting with version 9.1)