I am trying to follow the example given below, but in reverse. I have something like table2, that I am trying to "un-pivot" to look like table1… And I seem to be having a booger of a time. My attempt: SELECT * FROM crosstab( 'SELECT rowid, key1,key1 FROM test WHERE key1= ''value'' OR key2 = ''value'' ORDER BY 1,2' ) AS ct(rowid text, value int8); Can anyone point out where my small brain is failing me? PostgreSQL crosstab query - Rotate a table about a pivotAn 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); |
