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: Pivoting tables?
Date Wed, 21 Nov 2012 14:40:04 GMT
 
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

Mime
View raw message