SQL crosstab

[jdbruijn]

For use in a jasper report I need to transpose the data from the following (simplified) query:

Code: Select all

select process_name, count(id), sum(done)
from myTable
group by process_name


Where I don't know how many processes there are
This is the result of that query

Code: Select all

process_name | count | sum
-------------|-------|----
process_1    |    20 |  10
process_2    |    30 |   9
process_3    |    11 |   9
....
process_N    |     x |   x

For use in the report I need it in the following format:

Code: Select all

      | process_1 | process_2 | process_3 | ... | process_N
-----------------------------------------------------------
count |        20 |        30 |        11 |     |         x
sum   |        10 |         9 |         9 |     |         y

I've been looking at postgresql crosstab function, but that requires a knowledge of the nr of resulting columns. Is there anyother solution to this?

[Bernd.N]

It might be I do not see the real task, but after your initial SELECT you do have the nr of the resulting columns, or don't you?
As then you can put together the needed SQL statement in a string and fire it with plugins.rawSQL.executeSQL( or any Servoy statement that does this too), creating the (temporary) table you need.

If there were no crosstab function, you could also create the second table from scratch with a loop and some SQL statements you put together.

[erdione]

what don't you simply use jasper cross table component?

[jdbruijn]

what don't you simply use jasper cross table component?

I'm looking into that at the moment, but I cannot find how I can remove the totals row (and keep the totals column)