Gabriel Filipiak Gabriel Filipiak - 29 days ago 8
SQL Question

PostgreSQL and pivot tables using crosstab function

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;

Answer

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