Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 252769F9838 for ; Tue, 14 Aug 2007 10:36:34 -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 95629-08 for ; Tue, 14 Aug 2007 10:36:12 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from coe.tamu.edu (edsun.coe.tamu.edu [165.91.232.6]) by postgresql.org (Postfix) with ESMTP id 705279F9E82 for ; Tue, 14 Aug 2007 10:36:29 -0300 (ADT) Received: (from root@localhost) by coe.tamu.edu (8.11.7p3+Sun/8.12.11) id l7EDZOB09807 for pgsql-admin@postgresql.org; Tue, 14 Aug 2007 08:35:24 -0500 (CDT) Received: from coe.tamu.edu (coe.tamu.edu [165.91.232.6]) by coe.tamu.edu (8.11.7p3+Sun/8.12.11) with ESMTP id l7EDY3C09724; Tue, 14 Aug 2007 08:34:03 -0500 (CDT) Date: Tue, 14 Aug 2007 08:34:02 -0500 (CDT) From: Ben Kim To: giuseppe.derossi@email.it cc: pgsql-admin@postgresql.org Subject: Re: merge two rows where value are null In-Reply-To: <2fbf3fefb2389d08d2aedf3b60d85557@83.225.63.234> Message-ID: References: <2fbf3fefb2389d08d2aedf3b60d85557@83.225.63.234> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: by AMaViS 0.3.12 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/154 X-Sequence-Number: 26485 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