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 Display multiple rows into columns?

Discussion in 'SQL PL/SQL' started by kiranmcaa, May 24, 2012.

  1. kiranmcaa

    kiranmcaa Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    consider the query
    select invoice_mth from table xdetails
    where mobile_number=9080808080

    data in the table

    mobile_number invoice_mth
    9080808080 2010-10
    9080808080 2010-11
    9080808080 2010-12

    i want the out put like this
    2010-10 2010-11 2010-12
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Which release of Oracle are you using? The answer depends on which release you are running.
     
  3. kiranmcaa

    kiranmcaa Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hi David We are using 11g

    data in the table

    mobile_number invoice_mth inv_amt
    9080808080 2010-10 20
    9080808080 2010-11 10
    9080808080 2010-12 15

    out put should be like this

    2010-10 2010-11 2010-12
    20 10 15
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You need to learn the listagg function, which is described here:

    http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions089.htm

    An example is shown below:

    Code (SQL):
    SQL> CREATE TABLE mobile_inv(
      2          mobile_number NUMBER,
      3          invoice_mth varchar2(10),
      4          inv_amt NUMBER);
     
    TABLE created.
     
    SQL>
    SQL> INSERT ALL
      2  INTO mobile_inv
      3  VALUES(9080808080, '2010-10', 20)
      4  INTO mobile_inv
      5  VALUES(9080808080, '2010-11', 10)
      6  INTO mobile_inv
      7  VALUES(9080808080, '2010-12', 15)
      8  INTO mobile_inv
      9  VALUES(9080808081, '2010-10', 40)
     10  INTO mobile_inv
     11  VALUES(9080808081, '2010-11', 60)
     12  INTO mobile_inv
     13  VALUES(9080808081, '2010-12', 45)
     14  INTO mobile_inv
     15  VALUES(9080808085, '2010-10', 70)
     16  INTO mobile_inv
     17  VALUES(9080808085, '2010-11', 80)
     18  INTO mobile_inv
     19  VALUES(9080808085, '2010-12', 25)
     20  SELECT * FROM dual;
     
    9 ROWS created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> COLUMN invoice_month format a30
    SQL> COLUMN inv_amt format a30
    SQL>
    SQL> SELECT mobile_number,
      2         listagg(invoice_mth, ' ') WITHIN GROUP (ORDER BY invoice_mth) invoice_month,
      3         listagg(inv_amt, ' ') WITHIN GROUP (ORDER BY invoice_mth, inv_amt) inv_amt
      4  FROM mobile_inv
      5  GROUP BY mobile_number;
     
    MOBILE_NUMBER INVOICE_MONTH                  INV_AMT
    ------------- ------------------------------ ------------------------------
       9080808080 2010-10 2010-11 2010-12        20 10 15
       9080808081 2010-10 2010-11 2010-12        40 60 45
       9080808085 2010-10 2010-11 2010-12        70 80 25
     
    SQL>
     
  5. kiranmcaa

    kiranmcaa Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hi David

    Thanks for ur response.

    i want invoice months to seperate each month and amt seperately.

    MOBILE_NUMBER INv_m1 inv_m2 inv_m3 INV_AMT1 INV_AMT1
    ------------- -------- -------- -------- ---------- -------------------------
    9080808080 2010-10 2010-11 2010-12 20 10

    if this is pnot posible in 1 querie then we want to take inv_mths1 in 1 querie and amts in other querie
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Then you will need to write some PL/SQL to get the output the way you want it.