Postgresql pivot? Crosstab?

Question

I have a table (which is the result of a query) in postgres that has a set of rows (the result of a complicated summation of data) that looks like the following: (The column names are the names of each day, and the value of each column is a double precision.)

Sun Mon Tues Wed Thurs Fri
1.24 1.11 4.51 3.21 2.21 1.01

I need to have the data selected from one row so the results look like the below:

Day   Amount
Sun   1.24
Mon   1.11
Tues 4.51
Wed   3.21
Thurs 2.21
Fri      1.01

I'm having difficulty just getting started, as I really need to change the column names to values and pivot the result. I tried experimenting with crosstab but I'm not entirely sure this is what I need. Any advice or suggestions that could get me going in the right direction would be very much appreciated.

related question is http://stackoverflow.com/questions/1128737/unpivot-and-postgresql — Unreason, Dec 01 '10 at 21:03
With *"experimenting with crosstab"* you are referring to the crosstab contrib module? — , Dec 01 '10 at 21:56
yes I was looking at the tablefunc.crosstab function - http://www.postgresql.org/docs/current/static/tablefunc.html — Tom Lerma, Dec 02 '10 at 16:12

Stew · Answer 1 · 2011-06-15T15:37:00.623

Modifying @Jack Douglas's first answer:

SELECT unnest(array['sun', 'mon', 'tue', 'wed', 'thu', 'fri']) AS day,
       unnest(array[sun, mon, tue, wed, thu, fri]) AS amount
FROM t;

A little less costly according to the 9.0 query planner:

Seq Scan on t (cost=0.00..11.62 rows=360 width=192)

versus

Subquery Scan on z (cost=0.00..12.16 rows=360 width=68) -> Seq Scan on t (cost=0.00..11.26 rows=360 width=192)

score 1 · Answer 2 · answered Dec 08 '10 at 09:08

test objects:

create table t ( sun numeric, 
                 mon numeric, 
                 tue numeric, 
                 wed numeric, 
                 thu numeric, 
                 fri numeric );

insert into t(sun, mon, tue, wed, thu, fri)
values(1.24, 1.11, 4.51, 3.21, 2.21, 1.01);

alternative to @Unreason's answer without a union:

select day[i], amount[i]
from ( select generate_series(1,6) as i, 
              array['Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri'] as day, 
              array[sun, mon, tue, wed, thu, fri] as amount
       from t ) z;

if you need to be more generic you could do something like this:

create or replace function unpivot(t) returns setof record 
                           language plpgsql immutable strict as $$
declare
  q record;
  r record;
begin
  for q in ( select attname, attnum 
             from pg_attribute 
             where attnum>0 and attrelid = ( select oid 
                                             from pg_class 
                                             where relname = 't' ) ) loop 
    for r in execute 'select '''||q.attname||'''::text, '||
                             '('||$1::text||'::t).'||q.attname||'::numeric' loop
      return next r;
    end loop;
  end loop;
  return;
end;$$;

select *
from unpivot((select row(t.*)::t from t)) 
       as foo(day text, amount numeric);

you can be a bit neater in 8.4 with a using clause in the execute but I can't test that as I am on 8.3

score 0 · Answer 3 · answered Dec 01 '10 at 21:05

I don't know of direct implementation but maybe something like http://www.mail-archive.com/dhis2-users@lists.launchpad.net/msg00109.html could get you started

Of course if you don't need a flexible solution you can do

SELECT 'Sun' AS Day, Sun AS Value FROM TABLE WHERE ...
UNION ALL
SELECT 'Mon' AS Day, Mon AS Value FROM TABLE WHERE ...
UNION ALL
SELECT 'Tue' AS Day, Tue AS Value FROM TABLE WHERE ...
UNION ALL
SELECT 'Wed' AS Day, Wed AS Value FROM TABLE WHERE ...
UNION ALL
SELECT 'Thu' AS Day, Thu AS Value FROM TABLE WHERE ...
UNION ALL
SELECT 'Fri' AS Day, Fri AS Value FROM TABLE WHERE ...

(Saturday?)

Postgresql pivot? Crosstab?

3 Answers3