Hello,
I have got several reports to write which involve some sort of transposition. Let me try to explain I have a table which is defined as
Table : results
id integer not null --- that's the pk
result_family integer --- that's an fk to a table in which I find a description defines the columns on the report
result_type integer --- that's an fk to a table in which I find a description defines the rows
result_value real
Say I have the following descriptions Fam1, Fam2 .... fam10 and type1, type2 ... type20
at the end the final report must look like
FAM1 FAM2 FAM5
Typ1 value value
type3 value
type6 value value
in other words, not every value exists. that's OK but my real gotcha is that I do not know when I start how many families exists nor how many types. On top of that the user can add families and types any time. On top of that I need to create adhoc reports on the fly (they represent water sample analysis to be performed and sample location; not every analysis will have the same sampling locations not the same analysis to perform)
I thought about creating an ad hoc temp table via execute for each report and then populate it in pl/sql via execute and return a setof records from that pl/sql function, but I am concerned about speed and load on the server and also the effect of creating several hundred temp tables (when generating these reports, I'll have to generate between 400 and 500 reports).
Another possibility would be to create one temp table with the max number of families as columns and populate it using execute o select the proper column.
These two idea seem a bit heavy to me, is there any other clever way of going about that ?
thanks in advance for your input
Didier
How would you go about that ?
