Postgres Pro
Downloads
Home   >   mailing lists

Thread: un-pivot with crosstab?

un-pivot with crosstab?

From
Kirk Wythers
Date:
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 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 -

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);

Re: un-pivot with crosstab?

From
David Johnston
Date:
Kirk Wythers wrote
> 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.

I see nothing in the tablefunc/crosstab documentation to suggest this can be
done.  You will need to use multiple queries and combine them using "UNION
ALL" to accomplish your goal.

David J.




--
View this message in context: http://postgresql.1045698.n5.nabble.com/un-pivot-with-crosstab-tp5738518p5738530.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.