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!

Get Data horizontally rather than vertically

Discussion in 'SQL PL/SQL' started by sharo, Jul 5, 2011.

  1. sharo

    sharo Active Member

    Messages:
    6
    Likes Received:
    1
    Trophy Points:
    65
    Hello,


    Consider the query :

    Select Dealer, Sales from DEALERDATA
    where period in (201106,201107)

    This will give result as:

    DEALER SALES

    Dealer1 100000
    Dealer1 200000
    Dealer2 600000
    Dealer2 700000

    I want result as

    DEALER SALES SALES
    Dealer1 100000 200000
    Dealer2 600000 700000

    If any one can let me know how this can be achieved?

    Many thanks,
    Sharo....
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This has been covered before; use the LAG() function:

    Code (SQL):
    SQL> CREATE TABLE dealerdata(
      2          dealer  varchar2(20),
      3          sales   NUMBER
      4  );
    TABLE created.
    SQL>
    SQL> INSERT ALL
      2  INTO dealerdata
      3  VALUES ('Dealer1', 100000)
      4  INTO dealerdata
      5  VALUES('Dealer1', 200000)
      6  INTO dealerdata
      7  VALUES('Dealer2', 600000)
      8  INTO dealerdata
      9  VALUES('Dealer2', 700000)
     10  SELECT * FROM dual;
    4 ROWS created.
    SQL>
    SQL> commit;
    Commit complete.
    SQL>
    SQL> SELECT dealer, lag(sales) OVER (partition BY dealer ORDER BY sales) sales1, sales sales2
      2  FROM dealerdata;
    DEALER                   SALES1     SALES2
    -------------------- ---------- ----------
    Dealer1                             100000
    Dealer1                  100000     200000
    Dealer2                             600000
    Dealer2                  600000     700000
    SQL>
    SQL> SELECT dealer, sales1, sales2
      2  FROM
      3  (SELECT dealer, lag(sales) OVER (partition BY dealer ORDER BY sales) sales1, sales sales2
      4  FROM dealerdata)
      5  WHERE sales1 IS NOT NULL
      6  ORDER BY 1;
    DEALER                   SALES1     SALES2
    -------------------- ---------- ----------
    Dealer1                  100000     200000
    Dealer2                  600000     700000
    SQL>
     
    sharo likes this.
  3. krisgopala.k

    krisgopala.k Active Member

    Messages:
    2
    Likes Received:
    1
    Trophy Points:
    85
    You haven't mentioned the database version. If 11g, you can use listagg function.

    select listagg(level,',') WITHIN GROUP (ORDER BY level) from dual
    connect by level < 10
     
    sharo likes this.
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Yes, it can work however you should provide a working example as you forgot to include the group by clause:

    Code (SQL):
    SQL> SELECT dealer, listagg(sales,' ') WITHIN GROUP (ORDER BY dealer) "Sales1  Sales2"
      2  FROM dealerdata
      3  GROUP BY dealer;
    DEALER               Sales1  Sales2
    -------------------- -------------------------
    Dealer1              100000 200000
    Dealer2              600000 700000
    SQL>
     
    sharo likes this.