How to create a table with Crosstab in Postgresql?

Asked

Viewed 56 times

0

I have a table tbexame and I want to create another one from a Crosstab, while studying I did the installation of the extension but I am not able to create a select so as to leave the result as follows:

table Crosstab:

paciente_id, parametro_exame_1, parametro_exame_2, ... ,parametro_exame_x
1, positivo, positivo, ...,parametro_exame_x
2, positivo, positivo, ...,parametro_exame_x
...
x, positivo, positivo, ...,parametro_exame_x

original table:

paciente_id, exame, valor
1, parametro_exame_1, positivo
1, parametro_exame_2, positivo
...
x, parametro_exame_x, positivo

Code of select Crosstab:

SELECT * FROM crosstab(
      'SELECT paciente_id, parametro_exame_1
       FROM   tbexame
       ORDER BY paciente_id ASC'
   ) AS ct (paciente_id character varying, exame TEXT);

Error:

return and sql tuple descriptions are incompatible
SQL state: 42601

Note: We do not know the value of x

Another way I tried was:

SELECT * FROM crosstab(
      'SELECT DISTINCT paciente_id, exame, resultado FROM tbexame ORDER BY paciente_id'
   ) AS ct (paciente_id character varying, exame character varying, resultado character varying);

But the result was an examination column and another result. And I actually wanted each parameter to be a column and the result as an attribute.

  • 1

    If you know the value of x then see thecrosstab_N_ function otherwise use crosstab(text source_sql, text category_sql) and in category_sql list possible examination parameters. See example in the documentation (F.38.1.4.).

  • You can make a SELECT DISTINCT exame FROM sua_tabela ORDER BY exame and get the relation of parametros_exame_n.

  • Siim the problem is that the parameter relation will be very large.

  • But from what I understand, this relationship is not the columns you want on your output?

  • I want there to be a patient column and a column for each parametro_exam.

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.