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

Answer 3

I was looking to do something similar to this to more easily process table information from a bash script. Turns out to be very easy to tell psql to display table columns as rows:

psql mydbname -x -A -F= -c "select * from blah where id=123"

• The -x is what pivots the output.
• The -A removes extra spacing.
• The -F= replaces | with = between the column name and the value.

This of course wont work in SQL, it only modifies how psql happens to format output.

Answer 4

I had a similar situation. I wrapped my query in a with statement and then did a bunch of UNION ALLs for each row. In my situation, if I had multiple records, the ncm_id would be different, so I went ahead and added that to my column list in my result set. This may not be the best way to do this, but it worked for my use case.

 WITH query_a AS (
     SELECT
       fin_item.item_number || ' - ' || fin_item.item_descrip1 fin_item,
       fin_ls.ls_number,
       ls_sort.sortby_employeeid,
       ls_sort.ncm_id,
       ls_sort.created_at,
       ls_sort.updated_at,
       ls_sort.sort_qty,
       ls_sort.initial_scan_time,
       ls_sort.ncm_scan_time,
       ls_sort.badge_scan_time,
       ls_sort.computer_name,
       ls_sort.number_of_ops,
       ls_sort.ncm_item_scan_time,
       sort_by.name sort_by,
       tblncm.instructions,
       tblncm.ncm_comments
     FROM public.item AS fin_item
       INNER JOIN public.ls AS fin_ls ON fin_item.item_id = fin_ls.ls_item_id
       INNER JOIN stone.ls_sort ON fin_ls.ls_id = ls_sort.ls_id
       INNER JOIN stone.vw_mssql_employees AS sort_by ON ls_sort.sortby_employeeid = sort_by.employeeid
       INNER JOIN stone.tblncm ON ls_sort.ncm_id = tblncm.ncm_id
       LEFT JOIN stone.equips AS mach_equips ON ls_sort.mach_equip_id = mach_equips.id
       LEFT JOIN stone.equips AS mold_equips ON ls_sort.mold_equip_id = mold_equips.id
     WHERE 1 = 1
           AND fin_ls.ls_number ILIKE 'S143027526190' || '%'
 )
   SELECT *
   FROM (
     (SELECT 'fin_item' my_column, fin_item::TEXT my_value, ncm_id::TEXT my_ncm FROM query_a)
      UNION ALL
     (SELECT 'ls_number' my_column, ls_number::TEXT my_value, ncm_id::TEXT my_ncm FROM query_a)
      UNION ALL
     (SELECT 'sortby_employeeid' my_column, sortby_employeeid::TEXT my_value, ncm_id::TEXT my_ncm FROM query_a)
      UNION ALL
     (SELECT 'ncm_id' my_column, ncm_id::TEXT my_value, ncm_id::TEXT my_ncm FROM query_a)
      UNION ALL
     (SELECT 'created_at' my_column, created_at::TEXT my_value, ncm_id::TEXT my_ncm FROM query_a)
      UNION ALL
     (SELECT 'updated_at' my_column, updated_at::TEXT my_value, ncm_id::TEXT my_ncm FROM query_a)
      UNION ALL
     (SELECT 'sort_qty' my_column, sort_qty::TEXT my_value, ncm_id::TEXT my_ncm FROM query_a)
      UNION ALL
     (SELECT 'initial_scan_time' my_column, initial_scan_time::TEXT my_value, ncm_id::TEXT my_ncm FROM query_a)
      UNION ALL
     (SELECT 'ncm_scan_time' my_column, ncm_scan_time::TEXT my_value, ncm_id::TEXT my_ncm FROM query_a)
      UNION ALL
     (SELECT 'badge_scan_time' my_column, badge_scan_time::TEXT my_value, ncm_id::TEXT my_ncm FROM query_a)
      UNION ALL
     (SELECT 'computer_name' my_column, computer_name::TEXT my_value, ncm_id::TEXT my_ncm FROM query_a)
      UNION ALL
     (SELECT 'number_of_ops' my_column, number_of_ops::TEXT my_value, ncm_id::TEXT my_ncm FROM query_a)
      UNION ALL
     (SELECT 'ncm_item_scan_time' my_column, ncm_item_scan_time::TEXT my_value, ncm_id::TEXT my_ncm FROM query_a)
      UNION ALL
     (SELECT 'sort_by' my_column, sort_by::TEXT my_value, ncm_id::TEXT my_ncm FROM query_a)
      UNION ALL
     (SELECT 'instructions' my_column, instructions::TEXT my_value, ncm_id::TEXT my_ncm FROM query_a)
      UNION ALL
     (SELECT 'ncm_comments' my_column, ncm_comments::TEXT my_value, ncm_id::TEXT my_ncm FROM query_a)
   ) as query_guy
 ORDER BY my_ncm;

Answer 5

I could be not understanding...but the way I did this was to also select DISTINCT then select the average columns values I needed. Like this:

SELECT DISTINCT contributing_factor_vehicle_1, AVG(number_of_cyclist_injured) FROM "table1".motor_vehicle_collisions_crashes 
GROUP BY contributing_factor_vehicle_1
ORDER BY avg(number_of_cyclist_injured) desc

That's what created something like so:

This was NYC motor vehicle crash data btw.

Answer 6

Here's a way of using hstore, much like Mickaël Le Baillif's answer, but a little bit more concise, and allows you to choose a subset of the columns dynamically, and shows how you can include more regular "hard coded" columns, like an ID, that tend to be required in more realistic cases.

-- Example wide data, similar to the question, but with an extra "id" column
WITH example_wide_data("id", "value1count", "value2count", "value3count") AS (
  VALUES 
    (1, 4, 5, 6),
    (2, 8, 9, 10)
)

SELECT
  id,
  r.key AS value_name,
  r.value AS value
FROM
  example_wide_data w,
  each(hstore(w.*)) AS r(key, value)
WHERE
  -- Chooses the columns to transpose dynamically
  -- Difference cases will likely need a different condition
  r.key ~ '^value[0-9]+count$';

Note there is a downside to the hstore approach: it casts the values to text along the way, so you might need to cast the values back to another type, depending on your use case.

Answer 7

In reading these answers, I feel that using WITH and UNION would be way simpler; No additional functions or stuff:

WITH tmp AS (SELECT val1, val2, val3 FROM view_name) 
   SELECT val1 FROM tmp UNION
   SELECT val2 FROM tmp UNION
   SELECT val3 FROM tmp;