Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 7 down vote accepted

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.

share|improve this answer

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)

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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