un-pivot with crosstab? - Mailing list pgsql-general

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 -

SELECT * FROM crosstab(
     'SELECT rowid, key, value 
           FROM test WHERE key= ''key1'' OR key = ''key2'' 
                       OR key = ''key3'' OR key = ''key4''
           ORDER BY 1,2'
     ) AS ct(rowid text, key1 text, key2 text,
                         key3 text, key4 text);