Forum OpenACS Development: Re: Crossing table rows to rows and columns

Collapse
2: Re: Crossing table rows to rows and columns (response to 1)
Posted by Dave Bauer on 09/03/20 05:11 AM
Iuri,

I recommend installing tablefunc. It's available as a PostgreSQL contrib module. OpenACS doesn't have any specific requirements for it, but there's a lot of create stuff in there, if you are still using PostgreSQL 9.

Look into how your OS installed the PostgreSQL contrib packages. Then you can just create a SQL upgrade script to install it into your database.

I've used tablefunc with OpenACS projects since PostgreSQL 9 was new.

Collapse
3: Re: Crossing table rows to rows and columns (response to 2)
Posted by Gustaf Neumann on 09/03/20 09:43 AM
Iuri, many PostgreSQL installations, many extensions are already included, can can do simply in psql:

CREATE EXTENSION IF NOT EXISTS tablefunc;

If this works, your are done, you can use the crosstab family

oacs-5-10=# \df crosstab
crosstab   crosstab2  crosstab3  crosstab4

otherwise progress as Dave suggested

see also:
https://stackoverflow.com/questions/3002499/postgresql-crosstab-query
https://www.vertabelo.com/blog/creating-pivot-tables-in-postgresql-using-the-crosstab-function/