How to Create Crosstab with Composite Row Name in PostgreSQL

pivotpostgresql

I have following table with id(intger), project (varchar), month (varchar), hours (integer)

CREATE TABLE foo (id,project,month,hours)
    AS VALUES
    ( 1 , 'A', 'aug', 1 ),
    ( 1 , 'A', 'sep', 3 ),
    ( 1 , 'B', 'aug', 2 ),
    ( 1 , 'B', 'sep', 5 ),
    ( 2 , 'A', 'aug', 2 ),
    ( 2 , 'A', 'sep', 4 );

I wanted below table 

|id | project | aug | sep |
|1  | A       | 1   | 3   |
|1  | B       | 2   | 5   |
|2  | A       | 2   | 4   |

I read this can be possible with an array but it seems like documentation is confusing. Can someone shed some light on this?

Best Answer

crosstab doesn't permit composite keys. it requires rows provide a unique identifier for which to pivot them on. This means in your case (id,project) has to be serialized. You can do this with jsonb, or array or the like. Here I'm just using a text[].

SELECT id, project, aug, sep
FROM crosstab(
  $$
    SELECT ARRAY[id,project]::text[], id, project, month, hours
    FROM foo
    ORDER BY 1
  $$,
  $$
    SELECT DISTINCT month FROM foo ORDER BY 1
  $$
) AS c(rn text[], id int,project text,aug int,sep int);

 id | project | aug | sep 
----+---------+-----+-----
  1 | A       |   1 |   3
  1 | B       |   2 |   5
  2 | A       |   2 |   4
(3 rows)

A couple of notes,

  • you should probably be storing month as a date.
  • you may also want to look at the MADlib pivot which does permit composite keys.

There are lots of other ways you can solve this problem too without using crosstab,

SELECT id, project, jsonb_object_agg( month, hours ) AS month_hours
FROM foo
GROUP BY id, project
ORDER BY 1,2;
 id | project |     month_hours      
----+---------+----------------------
  1 | A       | {"aug": 1, "sep": 3}
  1 | B       | {"aug": 2, "sep": 5}
  2 | A       | {"aug": 2, "sep": 4}
(3 rows)
Related Question