PostgreSQL and pivot tables using crosstab function

Question

I have a problem with creating a pivot table in PostgreSQL using the crosstab() function. It works well but it produces multiple records for the same client_id. How can I avoid this?

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);

//Edit

Thx to Erwin it works now, but I have tried to do this without built in function with no luck. Can someone suggest something on that? My code for only two years:

SELECT DISTINCT o.client_id, CASE WHEN (extract(year from o.date)=2001) THEN sum(o.amount) ELSE 0 END, CASE WHEN (extract(year from o.date)=2002) THEN sum(o.amount) ELSE 0 END FROM orders AS o GROUP BY 1, extract(year from o.date) ORDER BY 1;

Community · Accepted Answer · 2020-06-20 09:12:55Z

You need to ORDER BY the first query accordingly. I use the simplified syntax ORDER BY <ordinal number> here.

SELECT * FROM crosstab( 'SELECT client_id ,extract(year from date) ,sum(amount) FROM orders GROUP BY 1,2 ORDER BY 1,2', 'SELECT extract(year from date) FROM orders GROUP BY 1 ORDER BY 1') 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);

The crosstab() function is not included in standard PostgreSQL but comes with the additional module tablefunc.

Edit for additional request

Version without crosstab() function: Only group by client_id or you will end up with multiple rows per client_id.

SELECT client_id ,sum(CASE WHEN extract(year from date) = 2001 THEN amount ELSE 0 END) AS year_2001 ,sum(CASE WHEN extract(year from date) = 2002 THEN amount ELSE 0 END) AS year_2002 -- ... FROM orders o GROUP BY 1 ORDER BY 1;

Thx a lot, it is working now. After solving this I have tried to do this without built in function but no luck, can you look at my edit and try to help me?
@GabrielFilipiak: I amended my answer with a non-crosstab version.

Collectives™ on Stack Overflow

PostgreSQL and pivot tables using crosstab function

1 Answer 1

Edit for additional request

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Edit for additional request

4 Comments

Linked

Related