Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id B0F6F9F9EE8 for ; Tue, 14 Aug 2007 10:48:40 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 01787-02 for ; Tue, 14 Aug 2007 10:48:23 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from smtp-out02.email.it (smtp-out02.email.it [212.97.34.22]) by postgresql.org (Postfix) with ESMTP id C79039F99DB for ; Tue, 14 Aug 2007 10:48:33 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by smtp-out02.email.it (Postfix) with ESMTP id 66D9E2C006; Tue, 14 Aug 2007 15:48:31 +0200 (CEST) X-Virus-Scanned: amavisd-new at email.it Received: from smtp-out02.email.it ([127.0.0.1]) by localhost (smtp-out02.email.it [127.0.0.1]) (amavisd-new, port 10024) with LMTP id UAxKMXpxm-74; Tue, 14 Aug 2007 15:48:30 +0200 (CEST) Date: Tue, 14 Aug 2007 15:48:38 +0200 To: Ben Kim From: giuseppe.derossi@email.it Cc: pgsql-admin@postgresql.org Reply-to: giuseppe.derossi@email.it Subject: Re: merge two rows where value are null Message-ID: X-Priority: X-Mailer: Webmail-Email.it [Mailer version 1.70] X-SenderIP: 83.225.75.94 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Content-Type: text/plain; charset="iso-8859-1" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/155 X-Sequence-Number: 26486 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 , but if there are some more values I get them all as well. thanks --------- Original Message -------- Da: Ben Kim 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