sql problem

[pras]

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

[cheland]

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

[pras]

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