| Difficult query!!! [message #21361] |
Tue, 30 July 2002 02:29  |
karunamoorthy_p
Messages: 13
Registered: April 2002
|
Junior Member |
|
|
I have data like this:- (in item_master table)
Drawing_No Item_No Quantity
---------- ------- --------
drg01 it01 100
drg01 it02 200
drg02 it01 300
drg02 it03 150
.... .... ...
.... .... ...
.... .... ...
Now I want the result like this:-
Drawing_No It01 It02 It03 Sum_Qty
---------- ----- ----- ----- -------
drg01 100 200 0 300
drg01 300 150 0 450
..... ... ... ... ...
..... ... ... ... ...
Could you please help me to solve this problem.
|
|
|
|
| Re: Difficult query!!! [message #21367 is a reply to message #21361] |
Tue, 30 July 2002 06:11  |
Jon
Messages: 483
Registered: May 2001
|
Senior Member |
|
|
SQL> create table table1 (drawing_no varchar2(20), item_no varchar2(20), quantity number);
Table created.
SQL> insert into table1 values ('drg01','it01',100);
1 row created.
SQL> insert into table1 values ('drg01','it02',200);
1 row created.
SQL> insert into table1 values ('drg02','it01',300);
1 row created.
SQL> insert into table1 values ('drg02','it03',150);
1 row created.
SQL> select drawing_no, sum(decode(item_no,'it01',quantity,0)) It01,
2 sum(decode(item_no,'it02',quantity,0)) It02,
3 sum(decode(item_no,'it03',quantity,0)) It03,
4 sum(quantity) from table1
5 group by drawing_no;
DRAWING_NO IT01 IT02 IT03 SUM(QUANTITY)
-------------------- --------- --------- --------- -------------
drg01 100 200 0 300
drg02 300 0 150 450
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
