db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John English <john.fore...@gmail.com>
Subject Pivoting tables?
Date Wed, 21 Nov 2012 14:14:11 GMT
I have a situation where I have a table listing users and products and 
associated values:

   abc    xyz       3
   def    ghi       5
   def    xyz       7

and I want to pivot this to display it with a column for each product 
like so:

   USER   ghi  xyz
   abc         3
   def    5    7

This means that the columns I have depend on the product list, which 
changes pretty regularly (at least at certain times) and they also 
depend on which department you're visiting (each has a different product 
list). At the moment I use a temporary table:

     Object lock = null;
     synchronized (state.tempTables) { // "state" is from the HttpSession
       if (state.tempTables.get("products") == null) {
         state.tempTables.put("products",new Object());
       lock = state.tempTables.get("products");
     synchronized (lock) {
       // start transaction
       // drop the temporary table if it exists
       // create the temporary table
       // select rows from the real table
       while (res.next()) {
         // insert into temporary table
       // commit transaction
       // display the temporary table

This is ugly and slow, but I've been unable to come up with a better 
way. The table is dropped at the start rather than at the end because 
the user might choose to download it as CSV, so it's left in existence 
after it's displayed in case it's needed for this purpose.

I thought about using a table function, but again the column list is 
fixed when the function is defined.

Does anyone have any ideas what else I could try? Or is there anything 
in the pipeline for a future version that might be relevant?

John English

View raw message