Postgres: Use result of group by as column names 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?
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.