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