db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From <Malte.Kem...@de.equens.com>
Subject AW: AW: Pivoting tables?
Date Wed, 28 Nov 2012 14:30:34 GMT
 

Hi John,
It sounds to me that your first solution could work flexible enough, while the other might
have lecks of flexibility, haven't they?
I am not sure how much you like to studdy and translate it in derby and Java procedures in
the example of the page from last answer.
http://www.simple-talk.com/sql/t-sql-programming/creating-cross-tab-queries-and-pivot-tables-in-sql/
http://www.simple-talk.com/code/CrossTab/sys_CrossTab.txt



Malte

-----Urspr√ľngliche Nachricht-----
Von: John English [mailto:john.foreign@gmail.com] 
Gesendet: Mittwoch, 28. November 2012 11:29
An: derby-user@db.apache.org
Betreff: Re: AW: Pivoting tables?

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

Mime
View raw message