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 googled a bit and found examples (for other database-systems) but a bit modified it should
work also for derby.
Maybe with derby's case expression it could work.
Here an example
function.
SELECT YEAR(ord.OrderDate) YEAR,
SUM(CASE prod.CategoryID WHEN 1 THEN
det.UnitPrice * det.Quantity ELSE 0 END) Beverages,
SUM(CASE prod.CategoryID WHEN 2 THEN
det.UnitPrice * det.Quantity ELSE 0 END) Condiments,
SUM(CASE prod.CategoryID WHEN 3 THEN
det.UnitPrice * det.Quantity ELSE 0 END) Confections,
SUM(CASE prod.CategoryID WHEN 4 THEN
det.UnitPrice * det.Quantity ELSE 0 END) [Dairy Products],
SUM(CASE prod.CategoryID WHEN 5 THEN
det.UnitPrice * det.Quantity ELSE 0 END) [Grains/Cereals],
SUM(CASE prod.CategoryID WHEN 6 THEN
det.UnitPrice * det.Quantity ELSE 0 END) [Meat/Poultry],
SUM(CASE prod.CategoryID WHEN 7 THEN
det.UnitPrice * det.Quantity ELSE 0 END) Produce,
SUM(CASE prod.CategoryID WHEN 8 THEN
det.UnitPrice * det.Quantity ELSE 0 END) Seafood
FROM Orders ord
INNER JOIN [Order Details] det
ON det.OrderID = ord.OrderID
INNER JOIN Products prod
ON prod.ProductID = det.ProductID
GROUP BY YEAR(ord.OrderDate)
ORDER BY YEAR(ord.OrderDate)
Found in http://www.simple-talk.com/sql/t-sql-programming/creating-cross-tab-queries-and-pivot-tables-in-sql/
-----Ursprüngliche Nachricht-----
Von: John English [mailto:john.foreign@gmail.com]
Gesendet: Mittwoch, 21. November 2012 15:14
An: Derby Discussion
Betreff: Pivoting tables?
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
|