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.fore...@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.kem...@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
