Unanswered: how to create cross table in postgresql? urgent please.
hi friends, i want to knw hw to create cross table. i have the following table. name and city are the columns.
i want to retrieve values like chennai as column and it should contains all the name who belong to chennai and bangalore as another column which contains all the names of those people who belong to bangalore. see, row's values becoming column's name.
we can do it SQL server by using "pivot" statement. how to do in Postgresql. Please help me out.
A cross tab query is a transformation of rows of data to columns. It usually involves aggregation of data e.g. totals broken down by months, products etc., where the months are represented by columns.
So you can try
SUM(CASE WHEN purchase_date BETWEEN '2004-08-01' and '2004-08-31' THEN amount ELSE 0 END) As m2004_08,
SUM(CASE WHEN purchase_date BETWEEN '2004-09-01' and '2004-09-30' THEN amount ELSE 0 END) As m2004_09,
SUM(CASE WHEN purchase_date BETWEEN '2004-10-01' and '2004-10-31' THEN amount ELSE 0 END) As m2004_10,
SUM(amount) As Total
FROM purchases WHERE purchase_date BETWEEN '2004-08-01' AND '2004-10-31'
This is one example of cross table