2

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?

1
3

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)
2
  • I think this assumes that select distinct month... will output the months in the natural order, which it won't generally. Because of that, IHMO crosstab() is not very helpful in this case, whereas a 12-step CASE WHEN month=... THEN sum()... as "month-name" with just a GROUP BY would get the job done. Nov 29 '17 at 17:00
  • @DanielVérité it assumes and (now) enforces alphabetical order If he needs natural he should be using date. Nov 29 '17 at 17:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.