Thread: reporting and transposition

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 ?

thanks for the pointer

Didier