I have a situation where I have a table listing users and products and
associated values:
USER PRODUCT VALUE
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?
TIA,
--
John English
