Thread: merge two rows where value are null
Hi all, sorry if OT. I have the following using case: Name, Surname , Job, Hobby, Pet, address John, Smith, NULL, photo, NULL, NULL John, Smith, student, NULL, cat, NULL by using name and surname as selecting key, I want : John, Smith, student, photo, cat, NULL that is I want to substitute the null valus of the LAST row with not-null value of previous one. how can I perform that ? in may installed vesion there aren't function as merge and collect. Thanks in advance GIU -- Email.it, the professional e-mail, gratis per te: http://www.email.it/f Sponsor: Entra in Mondolastminute, centinaia di offerte ti aspettano per le tue vacanze a prezzi lastminute! Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=6851&d=20070814
On Tue, 14 Aug 2007, giuseppe.derossi@email.it wrote: > Name, Surname , Job, Hobby, Pet, address > John, Smith, NULL, photo, NULL, NULL > John, Smith, student, NULL, cat, NULL > by using name and surname as selecting key, I want : > > John, Smith, student, photo, cat, NULL If you are sure there's no conflict (multiple values) for a (name, surname) key, one simple solution might be (surely not an efficient one) select (select distinct Name from thetable where Name = 'name1' and Surname = 'surname1' where Name is not null), (select distinct Surname from thetable where Name = 'name1' and Surname = 'surname1' where Surname is not null), (select distinct Job from thetable where Name = 'name1' and Surname = 'surname1' where Job is not null), (select distinct Hobby from thetable where Name = 'name1' and Surname = 'surname1' where Hobby is not null), (select distinct Pet from thetable where Name = 'name1' and Surname = 'surname1' where Pet is not null), (select distinct address from thetable where Name = 'name1' and Surname = 'surname1' where address is not null) This will fail if you also have John, Smith, student, NULL, dog, NULL in addition to > John, Smith, student, NULL, cat, NULL You can test for offending rows by: select array_to_string( array( select distinct Pet from thetable where Name = 'name1' and Surname = 'surname1' where Pet is notnull),',') and so on... Regards, Ben K. Developer http://benix.tamu.edu
Thanks for the suggestion, I was studying a solution which foresees the 'case when' construct... now I can use it in addition to array_to_string . In fact my solution failed too if <it's raining cats and dogs :-) >, but if there are some more values I get them all as well. thanks --------- Original Message -------- Da: Ben Kim <bkim@tamu.edu> To: Cc: pgsql-admin@postgresql.org Oggetto: Re: [ADMIN] merge two rows where value are null Data: 14/08/07 15:38 > > > > On Tue, 14 Aug 2007, giuseppe.derossi@email.it wrote: > > > Name, Surname , Job, Hobby, Pet, address > > John, Smith, NULL, photo, NULL, NULL > > John, Smith, student, NULL, cat, NULL > > > by using name and surname as selecting key, I want : > > > > John, Smith, student, photo, cat, NULL > > If you are sure there's no conflict (multiple values) for a (name, > surname) key, one simple solution might be (surely not an efficient one) > > select > (select distinct Name from thetable where Name = 'name1' and Surname = 'surname1' where Name is not null), > (select distinct Surname from thetable where Name = 'name1' and Surname = 'surname1' where Surname is not null), > (select distinct Job from thetable where Name = 'name1' and Surname = 'surname1' where Job is not null), > (select distinct Hobby from thetable where Name = 'name1' and Surname = 'surname1' where Hobby is not null), > (select distinct Pet from thetable where Name = 'name1' and Surname = 'surname1' where Pet is not null), > (select distinct address from thetable where Name = 'name1' and Surname = 'surname1' where address is not null) > > This will fail if you also have > John, Smith, student, NULL, dog, NULL > > in addition to > > John, Smith, student, NULL, cat, NULL > > You can test for offending rows by: > > select > array_to_string( array( select distinct Pet from thetable where Name = 'name1' and Surname = 'surname1' where Pet is not null),',') > > and so on... > > > Regards, > > Ben K. > Developer > http://benix.tamu.edu > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > > > -- Email.it, the professional e-mail, gratis per te: http://www.email.it/f Sponsor: Hai bisogno di contanti per realizzare i tuoi desideri? Prometeo ti propone prestiti da 1.500 a 31.000 Euro! Clicca qui per un preventivo immediato. Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=6916&d=20070814
>>> On Tue, Aug 14, 2007 at 8:48 AM, in message <b1a365afcf3059983eecc3db74a3cadf@83.225.75.94>, <giuseppe.derossi@email.it> wrote: > I was studying a solution which foresees the 'case when' construct... > now I can use it in addition to array_to_string . In fact my solution > failed too if <it's raining cats and dogs :-) >, but if there are some more > values I get them all as well. Is there a timestamp, date, or sequence number that you can use to break ties? Without something like that, I don't think you can do what you want with set-based logic -- you would need to do something procedural without any guarantee that you'd get the same results in two different runs. -Kevin