Pivot / crosstab with more than one value column

Question

I have a view that produces the following resultset:

CREATE TABLE foo
AS
  SELECT client_id, asset_type, current_value, future_value
  FROM ( VALUES
    ( 1, 0, 10 , 20 ),
    ( 1, 1, 5  , 10 ),
    ( 1, 2, 7  , 15 ),
    ( 2, 1, 0  , 2 ),
    ( 2, 2, 150, 300 )
  ) AS t(client_id, asset_type, current_value, future_value);

And I need to transform it into this:

client_id    a0_cur_val   a0_fut_val  a1_cur_val  a1_fut_val  ...
1            10           20          5           10          
2            NULL         NULL        0           2

I know how to do this if I use just the current_value column, using crosstab. How can I use current_value and future_value to produce new columns in the destination resultset? If I just add future_value column to the crosstab(text) query it complains about "invalid source data SQL statement".

I'm using PostgreSQL 9.3.6.

I can post the crosstab query using just one column if someone thinks it can be helpful. — Rodrigo Strauss, May 04 '15 at 13:28

score 9 · Accepted Answer · edited May 23 '17 at 12:25

9

One way would be to use a composite type:

CREATE TYPE i2 AS (a int, b int);

Or, for ad-hoc use (registers the type for the duration of the session):

CREATE TEMP TABLE i2 (a int, b int);

Then run the crosstab as you know it and decompose the composite type:

SELECT client_id
     , (a0).a AS a0_cur_val, (a0).b AS a0_fut_val
     , (a1).a AS a1_cur_val, (a1).b AS a1_fut_val
     , (a2).a AS a2_cur_val, (a2).b AS a2_fut_val
FROM   crosstab(
       'SELECT client_id, asset_type, (current_value, future_value)::i2
        FROM   foo
        ORDER  BY 1,2'

      ,'SELECT * FROM generate_series(0,2)'
   ) AS ct (client_id int, a0 i2, a1 i2, a2 i2);

All parentheses are required!

Basics for crosstab():

PostgreSQL Crosstab Query

edited May 23 '17 at 12:25

Community

1
1

answered May 04 '15 at 14:00

Erwin Brandstetter

605,456
145
1,078
1,228

1

I think an ARRAY or even a POINT makes a lot more sense here, but cool answer. – Evan Carroll Apr 14 '17 at 20:13
1

@EvanCarroll: Yes, when all columns happen to have the same data type or it's ok to cast to `text`. The above works for *any* combination of types. – Erwin Brandstetter Apr 17 '17 at 22:12

Atreide · Answer 2 · 2022-01-11T10:59:53.587

Another option would be to construct a join out of the two crosstabs queries that you can use to recover any of the two sets of values independently... Meaning:

select coalesce(cur.client_id, fut.client_id) client_id
, c0, f0, c1, f1, c2, f2
from
(select client_id, c0, c1, c2
from crosstab 
    ('select client_id, asset_type, current_value
    from foo
    order by client_id, asset_type')
as sal1 (client_id int4, c0 int4 , c1 int4 , c2 int4)) cur
full outer join 
(select client_id, f0, f1, f2
from crosstab 
    ('select client_id, asset_type, future_value
    from foo
    order by client_id, asset_type')
as sal1 (client_id int4, f0 int4 , f1 int4 , f2 int4)) fut
on fut.client_id = cur.client_id

Meaning... Get current_value and future_value in two different crosstab queries and then join them to get the result in a join query

I used full outer join and coalesce for the client_id just in case any of the clients could not be present in first query containing the current value, if we would know current_value is always present we could do with left join and if both, current and future values were required then inner join would do

Pivot / crosstab with more than one value column

2 Answers2

Linked