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:
row ID – this column contains values identifying the resulting (rotated) row;
category – unique values in this column determine the columns of the rotated table. Let’s note that, unlike PIVOT , these values themselves are of no significance; important is their number only, since it specifies the maximal possible number of columns;
category value – the actual category values. The values are put in columns from left to right, and the category names play no role – just their order determined by the way the query is sorted is important.
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:
Console
Execute
SELECT b_q_id, v_color, SUM(b_vol) qty FROM utb JOIN utv ON b_v_id = v_id
WHERE b_q_id BETWEEN12AND16
GROUPBY b_q_id, v_color
ORDERBY b_q_id, CASE v_color WHEN'R'THEN1WHEN'G'THEN2ELSE3END;
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:
b_q_id
v_color
Qty
12
R
255
12
G
255
12
B
255
13
B
123
14
R
50
14
B
111
15
R
100
15
G
100
16
G
100
16
B
150
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:
square
R
G
B
12
255
255
255
13
123
14
50
111
15
100
100
16
100
150
Now, let’s try to write a query using CROSSTAB that will return the desired result:
SELECT * FROM
crosstab(
$$select b_q_id, v_color, SUM(b_vol) qty FROM utb JOIN utv ON b_v_id = v_id
WHERE b_q_id BETWEEN12AND16
GROUPBY b_q_id, v_color
ORDERBY b_q_id, CASE v_color WHEN'R'THEN1WHEN'G'THEN2ELSE3END; $$
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):
square
R
G
B
12
255
255
255
13
123
14
50
111
15
100
100
16
100
150
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:
Console
Execute
SELECT b_q_id, v_color, SUM(b_vol) qty FROM(
SELECT b_q_id, v_color, b_vol FROM utb
JOIN utv ON b_v_id = v_id
UNIONALL--here addition is
SELECT * FROM(SELECTDISTINCT b_q_id FROM utb) X
CROSSJOIN(SELECT'R' color, NULL::smallint vol
UNIONALLSELECT'G', NULLUNIONALLSELECT'B', NULL) Y
) X
WHERE b_q_id BETWEEN12AND16
GROUPBY b_q_id, v_color
ORDERBY b_q_id, CASE v_color WHEN'R'THEN1WHEN'G'THEN2ELSE3END;
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:
SELECT * FROM
crosstab(
$$select b_q_id, v_color, SUM(b_vol) qty FROM utb JOIN utv ON b_v_id = v_id
WHERE b_q_id BETWEEN12AND16
GROUPBY b_q_id, v_color
ORDERBY b_q_id, CASE v_color WHEN'R'THEN1WHEN'G'THEN2ELSE3END; $$,
Since PostgreSQL allows the usage of table value constructors, the query
SELECT'R'UNIONALLSELECT'G'UNIONALLSELECT'B';
can be replaced by a shorter one:
VALUES('R'),('G'),('B');
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)
CREATE EXTENSION IFNOTEXISTS tablefunc;
that has to be executed for a given database. For versions prior to 9.1 you just need to copy the following file: share/contrib/tablefunc.sql to pgadmin, and execute it.