Saturday, October 15, 2011

PostgreSQL crosstab query - Rotate a table about a pivot

An interesting feature of relational databases(postgres in this case) is the ability to rotate the table about a pivot. So if you have data like this-
 id | rowid | key | value
---+------+----+-------
  1 | test1 | key1      | val1
  2 | test1 | key2      | val2
  3 | test1 | key3      | val3
  4 | test1 | key4      | val4
  5 | test2 | key1      | val5
  6 | test2 | key2      | val6
  7 | test2 | key3      | val7
  8 | test2 | key4      | val8

And want to have a result set like this -

rowid | key1 | key2 | key3 | key4
------+------+-----+-----+------
 test1  | val1  | val2  | val3  | val4
 test2  | val5  | val6  | val7  | val8


It can be achieved by a "crosstab" query in a postgres database -



Update: 02/14/2013
The following can be achieved by a crosstab and also by an interesting SQL which I came across here - http://stackoverflow.com/questions/14863985/postgres-crosstab-maybe


4 comments:

ffdavidson said...

What about if you have an indeterminate number of values, e.g., key1, key2,...kn, such that you can't specify them in the cross?

Kirk Wythers said...

What about the reverse? If you need to "un-pivot" table2 to get table1 as a result?

Harpreet Singh said...

Not sure, if that can be done via sql. Something to look into.

Anvesh Patel said...

Nice Article !
This is my pleasure to read your article.
Really this will help to people of PostgreSQL Community.

I have also prepared one article about, Create pivot table in PostgreSQL.
You can also visit my article, your comments and reviews are most welcome.

http://www.dbrnd.com/2016/09/postgresql-create-pivot-table-to-arrange-rows-into-columns-form-using-group-by-clause/