1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Decode--- Convert rows to columns

Discussion in 'SQL PL/SQL' started by preetpalkapoor, Jul 3, 2012.

  1. Hi All,

    Could someone please help me in getting a query.

    I am having a table with following data:


    Date Product_name Quantity_sold
    12/6/2012 Product1 1
    12/7/2012 product2 3
    12/6/2012 product3 4
    12/7/2012 Product1 2
    12/7/2012 product2 1
    12/6/2012 product3 5
    12/7/2012 product3 3
    12/6/2012 product2 4


    Now I want this data in following manner:

    For each date - how much is the Total quantity sold , sum of Product1 sold,Sum of product2 sold,etc.
    I am having more than 50 products in my table.

    date Quantity_sold Product1 Product2 Product3
    12/6/2012 14 1 4 9
    12/7/2012 9 2 4 3


    I was trying this by decode but the sum of the product was not correct.


    could someone please help me on this!!!!

    Thanks,
    Preetpal
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,349
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Decode won't help you as you can see by the examples. Analytic functions, though, might be the key to success:

    Code (SQL):
    SQL> SELECT prod_dt, decode(prod_name, 'Product1', quantity_sold) "Product1",
      2                  decode(prod_name, 'Product2', quantity_sold) "Product2",
      3                  decode(prod_name, 'Product3', quantity_sold) "Product3"
      4  FROM product_sales;
     
    PROD_DT     Product1   Product2   Product3
    --------- ---------- ---------- ----------
    06-DEC-12          1
    07-DEC-12                     3
    06-DEC-12                                4
    07-DEC-12          2
    07-DEC-12                     1
    06-DEC-12                                5
    07-DEC-12                                3
    06-DEC-12                     4
     
    8 ROWS selected.
     
    SQL>
    SQL> SELECT x.prod_dt, decode(prod_name, 'Product1', quantity_sum) "Product1",
      2                  decode(prod_name, 'Product2', quantity_sum) "Product2",
      3                  decode(prod_name, 'Product3', quantity_sum) "Product3",
      4                  quantity_ttl
      5  FROM (SELECT prod_dt, prod_name, SUM(quantity_sold) quantity_sum FROM product_sales GROUP BY prod_dt, prod_name ORDER BY prod_dt) x
      6  (SELECT prod_dt, SUM(quantity_sold) quantity_ttl FROM product_sales GROUP BY prod_dt ORDER BY prod_dt) y
      7  WHERE y.prod_dt = x.prod_dt;
     
    PROD_DT     Product1   Product2   Product3 QUANTITY_TTL
    --------- ---------- ---------- ---------- ------------
    06-DEC-12                                9           14
    06-DEC-12                     4                      14
    06-DEC-12          1                                 14
    07-DEC-12                                3            9
    07-DEC-12                     4                       9
    07-DEC-12          2                                  9
     
    6 ROWS selected.
     
    SQL>
    SQL> SET linesize 132
    SQL> COLUMN prod_name format a40
    SQL> COLUMN quantity_sold format a20
    SQL>
    SQL> SELECT prod_dt, listagg(prod_name, ' ') WITHIN GROUP (ORDER BY prod_dt) prod_name,
      2                  listagg(quantity_sum, ' ') WITHIN GROUP (ORDER BY prod_dt, prod_name) quantity_sold
      3  FROM (SELECT prod_dt, prod_name, SUM(quantity_sold) quantity_sum FROM product_sales GROUP BY prod_dt, prod_name ORDER BY prod_dt)
      4  GROUP BY prod_dt;
     
    PROD_DT   PROD_NAME                                QUANTITY_SOLD
    --------- ---------------------------------------- --------------------
    06-DEC-12 Product1 Product2 Product3               1 4 9
    07-DEC-12 Product1 Product2 Product3               2 4 3
     
    SQL>
    SQL> SELECT DISTINCT prod_dt, prod_name, SUM(quantity_sold) OVER (partition BY prod_name ORDER BY prod_dt, prod_name DESC) qty_sold, SUM
    quantity_sold) OVER (partition BY prod_dt) qty_ttl
      2  FROM product_sales;
     
    PROD_DT   PROD_NAME                                  QTY_SOLD    QTY_TTL
    --------- ---------------------------------------- ---------- ----------
    06-DEC-12 Product3                                          9         14
    06-DEC-12 Product2                                          4         14
    06-DEC-12 Product1                                          1         14
    07-DEC-12 Product3                                         12          9
    07-DEC-12 Product2                                          8          9
    07-DEC-12 Product1                                          3          9
     
    6 ROWS selected.
     
    SQL>
    SQL> SELECT prod_dt, qty_ttl, listagg(prod_name, '  ') WITHIN GROUP (ORDER BY prod_dt) prod_name, listagg(qty_sold, '  ') WITHIN GROUP (
    rder BY prod_dt, prod_name) quantity_sold
      2  FROM
      3  (SELECT DISTINCT prod_dt, prod_name, SUM(quantity_sold) OVER (partition BY prod_name ORDER BY prod_dt, prod_name DESC) qty_sold, su
    (quantity_sold) OVER (partition BY prod_dt) qty_ttl
      4  FROM product_sales)
      5  GROUP BY prod_dt, qty_ttl;
     
    PROD_DT      QTY_TTL PROD_NAME                                QUANTITY_SOLD
    --------- ---------- ---------------------------------------- --------------------
    06-DEC-12         14 Product1  Product2  Product3             1  4  9
    07-DEC-12          9 Product1  Product2  Product3             3  8  12
     
    SQL>
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    This can be done by using DECODE also...

    Code (SQL):
    SQL> WITH sales AS
      2  ( SELECT TO_DATE ( '12/6/2012','dd/mm/yyyy') DT ,'product1' Product_name  , 1 Quantity_sold FROM dual UNION ALL
      3    SELECT TO_DATE ( '12/7/2012','dd/mm/yyyy'),'product2', 3 FROM dual UNION ALL
      4    SELECT TO_DATE ( '12/6/2012','dd/mm/yyyy'),'product3', 4 FROM dual UNION ALL
      5    SELECT TO_DATE ( '12/7/2012','dd/mm/yyyy'),'product1', 2 FROM dual UNION ALL
      6    SELECT TO_DATE ( '12/7/2012','dd/mm/yyyy'),'product2', 1 FROM dual UNION ALL
      7    SELECT TO_DATE ( '12/6/2012','dd/mm/yyyy'),'product3', 5 FROM dual UNION ALL
      8    SELECT TO_DATE ( '12/7/2012','dd/mm/yyyy'),'product3', 3 FROM dual UNION ALL
      9    SELECT TO_DATE ( '12/6/2012','dd/mm/yyyy'),'product2', 4 FROM dual )
     10  SELECT DT,
     11         SUM(decode(Product_name,'product1', Quantity_sold ))  P1,
     12         SUM(decode(Product_name,'product2', Quantity_sold )) P2,
     13         SUM(decode(Product_name,'product3', Quantity_sold )) P3,
     14         SUM( Quantity_sold ) TOTAL
     15  FROM sales
     16  GROUP BY DT ;

    DT                P1         P2         P3      TOTAL
    --------- ---------- ---------- ---------- ----------
    12-JUN-12          1          4          9         14
    12-JUL-12          2          4          3          9

    SQL>