Re: merge two rows where value are null

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Tue, 14 Aug 2007, giuseppe.derossi@xxxxxxxx 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

[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux