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!

How to get data in multiple columns from one table

Discussion in 'SQL PL/SQL' started by RIAZ, May 28, 2009.

  1. RIAZ

    RIAZ Forum Advisor

    Messages:
    54
    Likes Received:
    0
    Trophy Points:
    130
    hi all,

    I want to get the data from one table in the following format

    ID | APRIL TRANSACTIONS | MAY TRANSACTIONS
    1 | ABC | XYZ
    2 | LMN | RYZ

    Note*: TRANSACTIONS are not stored in separate columns for separate months.

    I tried to use the UNION operator but by using union i can't get the data in separate columns.

    Please help....
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    hi RIAZ, you need to provide create table code or at least describe the table and also some sample data so that we can make out what columns are there and how data is stored. Without that its impossible to understand how the data is stored in the table.
     
  3. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    Hi Riaz, do you have some identifier column which identifies the transaction period? You should have at least posted your query.
     
  4. RIAZ

    RIAZ Forum Advisor

    Messages:
    54
    Likes Received:
    0
    Trophy Points:
    130
    I got the solution and it works. Sharing with you people,

    Code (SQL):
    SELECT
    Item ,
    SUM(Period_1_qty) Period_1_qty ,
    SUM(Period_2_qty) Period_2_qty ,
    .
    .
    SUM(Period_n_qty) Period_n_qty                                                          
    FROM                                                          
    ( SELECT t.Item Item,
           Decode (t.Period, 1 , t.Qty , 0 ) Period_1_qty ,
           Decode (t.Period, 2 , t.Qty , 0 ) Period_2_qty ,
           .
           .
           Decode (t.Period, n , t.Qty , 0 ) Period_n_qty
    FROM TABLE t
    WHERE t.Period >= :period_from -- Parameter Value passed to query
      AND  t.Period <= :period_to  -- Parameter Value passed to query  )
    GROUP BY Item
    ORDER BY Item
     
  5. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Hi Riaz thanks for sharing but you didn't post the complete query. Morever in future remember the best way to ask help is when you give the create table and sample insert scripts and the query which is not working as you want. In that way we can analyse what's wrong better.

    All the best...
     
  6. RIAZ

    RIAZ Forum Advisor

    Messages:
    54
    Likes Received:
    0
    Trophy Points:
    130
    Thanks Sadik :)

    *The sample query i posted is just to share the logic otherwise the actual query is too long and will be time consuming to understand.
     
  7. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Sorry for late reply . . . . Could be referred by community.
    Can Make Much more simple. .. .

    Code (SQL):

    SELECT t.Item Item,
           SUM(Decode (t.Period, 1 , t.Qty , 0 )) Period_1_qty ,
           SUM(Decode (t.Period, 2 , t.Qty , 0 )) Period_2_qty ,
           .
           .
           SUM(Decode (t.Period, n , t.Qty , 0 )) Period_n_qty
    FROM TABLE t
    WHERE t.Period >= :period_from -- Parameter Value passed to query
      AND  t.Period <= :period_to  -- Parameter Value passed to query
    GROUP BY  t.Item