On Sun, Nov 6, 2011 at 9:06 AM, Gabriel Filipiak
<gabriel.filipiak@gmail.com> wrote:
I have problem with creating a pivot table in postgreSQL using crosstab function. It works well but it produces many records for the same client_id, how can I avoid it?
Here is the SQL:
SELECT * FROM crosstab('SELECT client_id,extract(year from date), sum(amount) from orders group by extract(
year from date), client_id','SELECT extract(year from date) FROM orders GROUP BY extract(year from date) order by extract(year from date)')
AS orders(
row_name integer,
year_2001 text,
year_2002 text,
year_2003 text,
year_2004 text,
year_2005 text,
year_2006 text,
year_2007 text,
year_2008 text,
year_2009 text,
year_2010 text,
year_2011 text);
I think it assumes all client_id rows will occur together, so as soon as it sees a different client_id, it moves to the next row. If it then encounters the original client_id, it creates yet another row for it. Add an order clause to your first query so that it will get all years for each client_id sequentially.