CODEWITHSUNDEEP
sqlpostgresql
Tom Lerma
Tom Lerma

Reputation: 203

Postgresql pivot? Crosstab?

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.

Upvotes: 6

Views: 5083

Answers (3)

Stew
Stew

Reputation: 4525

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)

Upvotes: 3

user533832
user533832

Reputation:

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

Upvotes: 1

Unreason
Unreason

Reputation: 12704

I don't know of direct implementation but maybe something like http://www.mail-archive.com/[email protected]/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?)

Upvotes: 0

Related Questions