Postgres: Use result of group by as column names

Question

I have a messages table in a database, which include a sender id and a message type (and of course many more columns not relevant for this question). I try to create a query which counts how many messages of each type a user have send.

e.g. if I have the following table:

---------------------------
id | user_id | message_type
---------------------------
1  | 1       | private
2  | 1       | public
3  | 1       | private
---------------------------

Then I want to get the following:

---------------------
id | private | public
---------------------
1  | 2       | 1
---------------------

So in fact I want to group by message_type and user_id, but instead of generating multiple rows per user, I want to create multiple columns, one for each message_type

Can I achieve this without hardcoding the message types in my query?

Answer 1

If you have a limited number of values that you want to convert into columns, then this can easily be implemented using an aggregate function with a CASE expression:

select user_id,
  sum(case when message_type = 'private' then 1 else 0 end) private,
  sum(case when message_type = 'public' then 1 else 0 end) public
from yourtable
group by user_id

See SQL Fiddle with Demo

PostgreSQL has the ability to use a crosstab() that can be used by installing the tablefunc module. This will perform a similar data transformation from rows into columns. Creating a dynamic version of the query is not a straight forward process, here is a great solution for a dynamic crosstab on StackOverflow.

Answer 2

PostgreSQL doesn't directly support PIVOT, which is the keyword usually used on other platforms for something like this. It does have some crosstab functions in the tablefunc module.

Using the crosstab() function, (an excellent StackOverflow answer)