Thread: Problem with pivot tables
I have problem with creating a pivot table in postgreSQL using crosstab function. It works well but it produces many
recordsfor the same client_id, how can I avoid it?<br /><br />Here is the SQL:<br /><br />SELECT * FROM
crosstab('SELECTclient_id,extract(year from date), sum(amount) from orders group by extract(<br /> year from date),
client_id','SELECTextract(year from date) FROM orders GROUP BY extract(year from date) order by extract(year from
date)')<br/>AS orders(<br />row_name integer, <br />year_2001 text, <br />year_2002 text, <br /> year_2003 text, <br
/>year_2004text, <br />year_2005 text, <br />year_2006 text, <br />year_2007 text, <br />year_2008 text, <br
/>year_2009text, <br />year_2010 text, <br />year_2011 text); <br /><br />best regard and thx in advance<br />
Gabe<br/>
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.