1

I have three tables in my database that I want to merge them and display them using crosstab function, please check this picture :
enter image description here

So what I'vre tried to do, by following this answer, is to join these three tables in one view and then use crosstab on it like this:

create or replace view t as
SELECT a.code as code_an, n.code as code_n, montant
FROM analytic a JOIN analytic_has_nature ON (a.id = id_analytic)
JOIN nature n ON (n.id = id_nature);
SELECT *
FROM   crosstab(
      'SELECT code_an, code_n, montant
       FROM   t
       ORDER  BY 1,2')
AS t2 (code_an character varying(50), code_n character varying(50), montant double precision);


unfortunately, Postgresql shows me this error:

ERREUR:  return and sql tuple descriptions are incompatible

What am I missing ?

Community
  • 1
  • 1
SlimenTN
  • 3,383
  • 7
  • 31
  • 78

1 Answers1

0

In the return tuple you have one column with code_n and three columns of type double precision. In your example code_n is a row identifier and code_an is a category, so replace them in the first query. Also, add the second query to define categories (it ensures the correct distribution of incomplete data):

SELECT *
FROM   crosstab(
      'SELECT code_n, code_an, montant
       FROM   t
       ORDER  BY 1,2',
      'VALUES (''PDR''), (''EQ''), (''DIV'')')
AS t2 ( code_n character varying(50), 
        "PDR" double precision,
        "EQ" double precision, 
        "DIV" double precision);
klin
  • 112,967
  • 15
  • 204
  • 232
  • ah thnx it works :), but is it possible to get those columns dynamicly ?I mean it can be more than 3 columns ex: PDR, EQ, DIV, SOP, ... – SlimenTN Dec 02 '15 at 14:53
  • Of course, you can `SELECT DISTINCT code_an FROM t ORDER BY 1` (or without order). – klin Dec 02 '15 at 14:54
  • You can use this `SELECT`as the second query but you have to explicitly write down column names and types in the return tuple type declaration, and ensure the order is the same. – klin Dec 02 '15 at 15:04