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:[email protected]]
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
