title: PostgreSQL and pivot tables using crosstab function
body: <p>I have a problem with creating a pivot table in PostgreSQL using the <code>crosstab()</code> function. It works well but it produces multiple records for the same <code>client_id</code>. How can I avoid this?</p>
  
  <p>Here is the SQL:</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>//Edit</p>
  
  <p>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:</p>
  
  <pre><code>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;
  </code></pre>