Difficult query!!!! [message #2592] |
Tue, 30 July 2002 02:34 |
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 #2596 is a reply to message #2592] |
Tue, 30 July 2002 07:10 |
ctg
Messages: 146 Registered: July 2002
|
Senior Member |
|
|
is there a limit to the number of items that each drawing can have? You can give this select stmt a try, it is set up for 3 item numbers.
SELECT drawing_no
,SUM(DECODE(item_no,'It01',quantity,0) as it01
,SUM(DECODE(item_no,'It02',quantity,0) as it02
,SUM(DECODE(item_no,'It03',quantity,0) as it03
,SUM(quantity) as sum_quantity
FROM item_master
GROUP BY drawing_no;
|
|
|