Re: consolidation of multiple rows
From: TheSQLGuru <kgboles_at_earthlink.net>
Date: Wed, 5 Mar 2008 10:24:27 -0600
Message-ID: <13sti97hotvn8c6@corp.supernews.com>
Date: Wed, 5 Mar 2008 10:24:27 -0600
Message-ID: <13sti97hotvn8c6@corp.supernews.com>
Perhaps a grouping/max setup? Something like this (untested):
select nname, max(address), max(phone)...
from raw_companies
group by nname
-- Kevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net <canoe414_at_telecom-digest.zzn.com> wrote in message news:4f04fa64-b865-4bf1-a7ed-498872b6bd74_at_u10g2000prn.googlegroups.com...Received on Wed Mar 05 2008 - 10:24:27 CST
> Greetings all,
>
> I've got a table raw_Companies
> --nname
> --address
> --phone
> --fax
> --homepage
>
> with no keys defined
> and a table Companies
> with the same structure but with nname declared as a primary key.
>
> Once raw_Companies gets loaded up I see where there are multiple rows
> with the same value for nname but missing data in the other fields.
> One row may have an address and phone while another will have only an
> email, while a third may have an address and a homepage.
>
> I need a way to take the rows in raw_Companies with the same nname and
> consolidate them into one composite row inserted into Companies.
>
> So from
>
> IBM----1234 Main Street,Rochester,NY----\N----\N----sales_at_ibm.com----
> www.ibm.com
> IBM----\N----212-745-9324----212-745-0158----\N----\N
>
> in raw_Companies to
>
> IBM----1234 Main
> Street,Rochester,NY----212-745-9324----212-745-0158----
> sales_at_ibm.com----www.ibm.com
>
> in Companies
>
> Any help/hints/suggestions/code would be greatly appreciated.
>
> TIA,
>
> Still-learning Steve