Postgres - pivot table / crosstab with multiple value columns

Question

I have a view that creates the following results:

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 convert it to 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 only use the current_value column using a crosstab. How to use current_value and future_value to create new columns in the target result set? If I simply add the future_value column to the crosstab(text) query, it complains of an “invalid source SQL query for the source data”.

I am using PostgreSQL 9.3.6.

Answer 1

One way is to use a composite type:

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

Or, to use ad-hoc (register type for session duration):

 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 needed!

The basics for crosstab() :

• PostgreSQL Cross Forward Request