Postgres Pivot Tables

Postgres doesn’t have a built-in pivot table feature, but they are relatively easy to construct.

Let’s say you have a table of users, and a table of events, and want to make a pivot table that shows how often each event occurred.

create table users (name varchar);

insert into users ('gary');
insert into users ('melissa');
insert into users ('ella');
insert into users ('chloe');

create table events (user_name varchar, event varchar);
insert 
into events (user_name, event) 
values ('gary', 'event_1');

insert 
into events (user_name, event) 
values ('gary', 'event_1');

insert 
into events (user_name, event) 
values ('gary', 'event_2');

insert 
into events (user_name, event) 
values ('melissa', 'event_2');

insert 
into events (user_name, event) 
values ('ella', 'event_1');

The pivot table output should look like this:

user     event_1  event 2
gary     2        1
melissa  0        2
ella     1        0
ziti     0        0

You can get counts easily enough, but the “0” records will drop out, which is a bit of a pain:

select 
  user_name, 
  event, 
  count(*) event_count
from events
group by 1, 2

What we really want is a list of all combinations of users and events, combined with their counts. Normally you don’t do things like this, but this can be obtained with a cartesian join.

You’ll want to make sure to itemize the names (don’t use select *) – this will help with the next step.

select users.name, event_list.event
from users
join ( 
  select distinct event 
  from events
) as event_list on 1=1

The above query gives us the following data:

gary	event_2
gary	event_1
melissa	event_2
melissa	event_1
ella	event_2
ella	event_1
chloe	event_2
chloe	event_1

If you have a static list of events, you can also join to it directly:

select users.name, event_list.event
from users
join ( 
  select unnest(array['event_1', 'event_2']) event
) as event_list on 1=1

Now that we have this, we can join it back to the count data:

select 
  users.name, 
  event_list.event, 
  coalesce(event_count, 0) event_count
from users
join (
   select distinct event 
   from events
  ) as event_list on 1=1
left join (
  select 
    user_name, 
    event, 
    count(*) event_count
  from events
  group by 1, 2
) counts on counts.user_name = users.name
        and counts.event = event_list.event

This new dataset is much better, as it includes every possible cell that we would want in the pivot table:

name    event     event_count
gary	event_1	  2
gary	event_2	  1
melissa	event_1	  0
melissa	event_2	  1
ella	event_1	  1
ella	event_2	  0
chloe	event_1	  0
chloe	event_2	  0

To get these all into one row, we can wrap this in array aggregation. Note that the values in the arrays need to be sorted by the event type, or else each record will get counts in a different order.

select 
  name, 
  array_agg(event order by event) events, 
  array_agg(event_count order by event) counts
from (
  ...
) aggregated_date
group by name

This returns:

name    events                  counts
chloe	{"event 1","event 2"}	{0,0}
ella	{"event 1","event 2"}	{1,0}
gary	{"event 1","event 2"}	{2,1}
melissa	{"event 1","event 2"}	{0,1}

Now getting pivot table data is easy through array indexing. Note that Postgres arrays start at 1 (!), and obviously this solution won’t help you if you have an indeterminite number of events.

select 
  name, 
  counts[1] as event_1, 
  counts[2] as event_2
from (
  ...
) pivot_data

name    event_1   event_2
chloe	0 	  0
ella	1	  0
gary	2	  1
melissa	0	  1

If you’re following along at home, here is the final result:

 -- Create pivot table
select
  name, 
  counts[1] as event_1, 
  counts[2] as event_2
from (
  -- Combine all the counts into one row per user
  select
    name, 
    array_agg(event order by event) events, 
    array_agg(event_count order by event) counts
  from (
    -- Create counts of "0" for events that haven't occurred
    select 
      users.name, 
      event_list.event, 
      coalesce(event_count, 0) event_count
    from users
    join (
       -- Compute the full list of events
       select distinct event 
       from events
    ) as event_list on 1 = 1
    left join (
      -- Compute how often each event happened per user
      select 
        user_name, 
        event, 
        count(*) as event_count
      from events
      group by 1, 2
    ) counts
       on counts.user_name = users.name
       and counts.event = event_list.event
  ) aggregated_date
  group by name
) pivot_data
order by name