sql problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: sql problem

  1. #1
    Join Date
    Jul 2000
    Location
    india
    Posts
    213
    Hi Everybody,

    Is there any thing called pivot tables in oracle.

    I am having a problem...

    i have created a table
    SQL>create table acc1(accid number(5),
    cr_dr varchar2(3),amount number(7,2));

    Table created.

    THEN I INSERTED SOME VALUES IN THAT TABLE

    SQL> insert into ACC1 values(1,'CR',100);

    1 row created.

    SQL> insert into ACC1 values(1,'DR',200);

    1 row created.

    SQL> SELECT * FROM ACC1;

    ACCID CR_ AMOUNT
    ---------- --- ----------
    1 CR 100
    1 DR 200

    Now my prob is i want output like

    ACCID CR DR
    --------- ------- -------
    1 100 200

    If any body have any idea pls help

    Thanks in advance
    pras

  2. #2
    Join Date
    May 2001
    Posts
    31
    Assuming that only two values ('CR','DR') exists for cr_dr, this will work:

    select accid,
    max(decode(cr_dr,'CR',amount)) "CR",
    max(decode(cr_dr,'DR',amount)) "DR"
    from acc1
    group by accid

  3. #3
    Join Date
    Jul 2000
    Location
    india
    Posts
    213

    Thanks

    Hi Cheland,


    Thanks a lot...Actually u showed me a way.There are many records in table and for every credit there ia a debit to some other accid.And i modified the query and it worked the way i want...Thanks again

    pras

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width