transpose column headers to rows in postgresql

Question

I have a view which looks like this

          value1count     value2count value3count
          ----------------------------------------
             25              35          55

I need to transpose the column header into rows and so I need it to look like

          Values              Count
         -----------------------------
           value1count         25
           value2count         35
           value3count         55

I can do this by selecting individual column names as first column and data as second column and then do a union of the same for all columns.

Is there a better way to do this? I am using PosgreSQL 8.1 and so don't have pivot operators to work with.

Thanks for your response in advance.

Answer 1

Crosstab only does the reverse of what you need, but this should help you:

First create the unnest() function that is included in 8.4, see here for instructions.

Then you can do this (based on this post):

SELECT
   unnest(array['value1Count', 'value2Count', 'value3Count']) AS "Values",
   unnest(array[value1Count, value2Count, value3Count]) AS "Count"
FROM view_name
ORDER BY "Values"

I can verify that this works in 8.4, but because I don't have 8.1, I can't promise it will work the same.

Answer 2

I achieved your goal by using hstore's functionalities :

SELECT (x).key, (x).value
FROM
  ( SELECT EACH(hstore(t)) as x
    FROM   t
  ) q;

May you have multiple rows in your "to-be-exploded" view or table (referred here as t), you might need to insert an additionnal identifier in the intermediate table q, for example:

SELECT id, (x).key, (x).value
FROM
  ( SELECT id, EACH(hstore(t)) as x
    FROM   t
  ) q;

Reference: hstore documentation