Re: consolidation of multiple rows

On Mar 5, 11:19 am, canoe..._at_telecom-digest.zzn.com wrote:
> Greetings all,
>
> I've got a table raw_Companies
> --nname
> --address
> --phone
> --fax
> --email
> --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----sa..._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----
> sa..._at_ibm.com----www.ibm.com
>
> in Companies
>
> Any help/hints/suggestions/code would be greatly appreciated.
>
> TIA,
>
> Still-learning Steve

A table like raw_Companies is called a staging table and Companies is the production table.
If you want the data to load automatically into companies, a PL/SQL procedure or trigger is one way to do it.

You can audit the changes (e.g. put in a log table messages like 'ABC Inc. : street address changed from 1234 Main Street to 123A Main Street") as well as logging errors (e.g., Update failed), checking data consistency (Zip code 11234 is invalid for Ohio), and even outside data validation (look up address in the USPS yields Zip code but also indicates the address is at least valid).

So it's time to step out of straight SQL and into PL/SQL.

   HTH,    Ed Received on Wed Mar 05 2008 - 11:43:10 CST