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?
Best Answer
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: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.