crosstab postgres - line to column

Question

On my project, the data model changed and I have to do a new request to get information but in the past it looks like all data are in the same line, but now a part of that data are in an other table, and it returns me column.

So my request returns:

I thought it was easy but i don't really understand how the function crosstab works. I tried this:

    SELECT *
    FROM crosstab(
      'SELECT c.cd_type as typcou, c.dt_envoi as dtenvcou, c.commentaire as comment, c.signataire as signur
           FROM comm.courrier c
           WHERE c.id_formalite=1
     order by 1,2',
    'select lf.valeur as valeur from formalite.ligne_formalite lf, formalite.formalite f where lf.fk_formalite=f.id AND lf.fk_formalite=1'
     )

AS c(typcou text, dtenvcou text, comment text, signataire text, valeur1 text, valeur2 text, valeur3 text);

However, I got

ERREUR: invalid return type État SQL :42601 Détail :Query-specified return tuple has 7 columns but crosstab returns 5.

Can someone help me figure out what's wrong?

What you want (based on given screenshot) is hardly possible : you can't match a specific value for a row to be a value for a new column (you ave no way to set the n-th value to the n-th column). You shoud make a specific script to modify you table schema. — Arnaud Rinquin, Dec 05 '12 at 14:40
but when i go to the help of postgres, they say that you can use crosstab to do these http://docs.postgresqlfr.org/9.0/tablefunc.html — user1879231, Dec 05 '12 at 14:53

score 1 · Answer 1 · edited May 23 '17 at 12:12

You failed to mention the column formalite.libelle in your question, which is essential for the solution.

I propose to use explicit JOIN syntax for your main query - generally easier to read and understand.

Since you have to spell out all columns in a column definition list anyway, it is more efficient to use a VALUES expression than SELECT DISTINCT ... for the second parameter of the crosstab() function:

SELECT *
FROM crosstab(
  'SELECT c.cd_type, c.dt_envoi, c.commentaire, c.signataire
        , r.libelle, lf.valeur
   FROM   comm.courrier             c
   JOIN   formalite.formalite       f  ON f.id  = c.id_formalite
   JOIN   formalite.ligne_formalite lf ON lf.fk_formalite = f.id
   JOIN   norme.rubrique            r  ON r.id = lf.id_rubrique 
   WHERE  c.id_formalite = 1
   ORDER  BY 1'

   ,$$VALUES ('codepost'::text), ('bureau'), ('date')$$
   )
AS (typcou char, dtenvcou text, comment text, signur text
  , codepost text, bureau text, date text);

More explanation and links in this closely related answer.

Aside: I wouldn't use date as column name ,it's a reserved word in SQL and a type name in PostgreSQL.

score 0 · Answer 2 · answered Dec 05 '12 at 15:17

I got it ! :

SELECT *
FROM crosstab(
  'SELECT c.cd_type as typcou, c.dt_envoi as dtenvcou, c.commentaire as comment, c.signataire as signur, r.libelle, lf.valeur
       FROM comm.courrier c, formalite.ligne_formalite lf, formalite.formalite f, norme.rubrique r
       WHERE c.id_formalite=f.id AND lf.fk_formalite=f.id
       AND c.id_formalite=1 AND r.id = lf.id_rubrique ORDER BY 1',
       'SELECT DISTINCT libelle FROM norme.rubrique ORDER BY 1')
AS (typcou char, dtenvcou text, comment text, signur text, codepost text, bureau text, date text);

score 0 · Answer 3 · answered Dec 05 '12 at 15:19

Try some open source ETL studio in order of preference:

talend
pentaho
jaspersoft

especially if you will have to do this often for large data volumes, but I am wondering, should you really be using Postgresql? You can also use these tools to migrate from one database server to another, should you wish.

crosstab postgres - line to column

3 Answers3