PostgreSQL et tables pivot en utilisant la fonction crosstab

Question

J'ai un problème avec la création d'une table pivot en PostgreSQL en utilisant la fonction crosstab(). Cela fonctionne bien mais cela produit plusieurs enregistrements pour le même client_id. Comment puis-je éviter cela?

Voici le 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

Merci à Erwin ça marche maintenant, mais j'ai essayé de le faire sans la fonction intégrée sans succès. Est-ce que quelqu'un peut suggérer quelque chose à ce sujet? Mon code pour seulement deux années :

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 1

Vous devez ORDER BY la première requête en conséquence. J'utilise la syntaxe simplifiée ORDER BY ici.

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

La fonction crosstab() n'est pas incluse dans PostgreSQL standard mais est fournie avec le module supplémentaire tablefunc.

Édition pour une demande supplémentaire

Version sans la fonction crosstab() : Groupez uniquement par client_id ou vous obtiendrez plusieurs lignes par 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;