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 Re: AW: Pivoting tables?
Date Wed, 28 Nov 2012 10:29:14 GMT
On 21/11/2012 16:40, Malte.Kempff@de.equens.com wrote:
> Hi John,
> I think a smart SQL-Statement schould do.
> OK that would probably a bit inflxible, since you have a lot of changes concerning users
and products.
> But you could may be write a SQL-Generating routine that takes care of it.

I've been playing around a bit with this; it helps, but doesn't quite 
hit the spot for me. So here I am again...

The problem for me is not so much gathering the results as presenting 
them. I have a monstrous method to display a table (or view), which 
builds the SQL query and appends WHERE and ORDER BY clauses as needed. 
This works fine, for cases where I can define a table or a view of a 
table, which means a fixed set of columns. I use temporary tables in
the cases where the table has to be generated dynamically (when I want
to pivot rows into columns), but as I said before, this is slow and ugly.

So apart from a temporary table, I thought about these possible solutions:

1) Create a real table every time a department is created and then use 
triggers to add and drop columns as products are added and removed, and 
to copy values when the "main" table is updated. The set of departments 
changes relatively slowly, but it does change; the products go through 
flurries of activity. I'm not sure how practical this is.

2) Use a function to return a virtual table. The problem here is that 
the table format is fixed when the function is declared. I could perhaps 
declare functions that return tables with maybe 100 columns and then 
hope that no-one ever needs 101 products, but this isn't very 
satisfactory either.

3) I've just been reading about procedures that return result sets, but 
then the problem is that I can't decorate the query (a CALL rather than 
a SELECT) with WHERE and ORDER BY clauses.

So for now, I'm still stuck.

Your suggestions and advice gratefully appreciated!
John English

View raw message