Re: Problem with pivot tables

On Sun, Nov 6, 2011 at 9:06 AM, Gabriel Filipiak <gabriel(dot)filipiak(at)gmail(dot)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.