Home » SQL & PL/SQL » SQL & PL/SQL » Regroup table rows by id (10G)
Regroup table rows by id [message #389298] Sat, 28 February 2009 05:08 Go to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Hi,

I have a table with the following values

create table inventory (
  inv_id number,
  cost number,
  bill varchar2(1));

insert into inventory values (1,20,null);
insert into inventory values (1,null,'b');
insert into inventory values (2,null,'b');
insert into inventory values (3,null,'c');
insert into inventory values (3,2,null);

commit;



Each id will have at most two records
If two records exists for each id, either inventory_cost or bill column will be null for both records, example:
if the first record has inventory_cost null,the second record will have a value for inventory_cost, same goes to bill column

Is there any way i could group the data by id column and produce the following results

id | cost           | bill
1  | 20             | b
2  | null           | b
3  | 2              | c
Re: Regroup table rows by id [message #389303 is a reply to message #389298] Sat, 28 February 2009 05:18 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
based on your sample data, a simple max on the cost and bill columns would return your stated required resultset
Re: Regroup table rows by id [message #389314 is a reply to message #389303] Sat, 28 February 2009 06:24 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Its unbelivable.
select inv_id, max(cost), max(bill) from inventory
group by inv_id


was not aware max on a value compared to null returns the value.I though max only works on numbers
Re: Regroup table rows by id [message #389331 is a reply to message #389314] Sat, 28 February 2009 12:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is right but this NULL is NULL of datatype NUMBER.

Regards
Michel
Re: Regroup table rows by id [message #389338 is a reply to message #389331] Sat, 28 February 2009 19:03 Go to previous message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
noted
Previous Topic: How to show oracle views data into Microsoft Exel Sheet (merged)
Next Topic: Read from long field more then 4000 bytes to varchar (merged)
Goto Forum:
  


Current Time: Mon Dec 05 09:03:54 CST 2016

Total time taken to generate the page: 0.10410 seconds