mysql get data from one table to another, while grouping records and extra fields

ok, i will explain first what i have, and what i need as result.

basicly, i have a phonebook, but the phonebook has duplicate records for "company" while the duplicate records are not exactly duplicate... :\

i think an example will explain better:

demo records:

1. Company A, Address A, Phone A
2. Company A, Address A, Phone B
3. Company A, Address A, Phone C
4. Company A, Address B, Phone A

what i want, is to make a new table, which will be filled with all "Company A", "Address A" phone numbers into one records, so the result should be something like: Company A, Address A, Phone A, Phone B, Phone C etc..

The second problem is that "company A" can be also a bit diffrent, example:

 1. Company XYZ, Address开发者_JAVA百科 A, Phone A

 2. Company ABC, Address A, Phone A

i dont even know how to start with this. i was thinking of doing it by coding some c# application, but i think there might be an easier solution by using pure mysql.

Your way does not maintain normalization,
however, the following might what you are looking for

create new table:

create table new_table
(
  company_a varchar(255),
  address_a varchar(255),
  phone_a varchar(255),
  phone_b varchar(255),
  phone_c varchar(255)
); // define your index

insert into new table:

assuming phone does not contains ,

insert into new_table
select 
  company, 
  address, 
  substring_index(group_concat(phone), ',', 1) 
    as phone_a,

  substring_index(
    substring_index(group_concat(phone), ',', 2), ',', -1
  ) as phone_b,

  substring_index(group_concat(phone), ',', -1)
from 
  old_table
group by company, address;

Not exactly an answer but a different approach

Why not have

• A table company

company | company_id | ...

• A table address

address_id | company_id | ...

• And a table phone

phone_id | address_id | ...

This way you can have as many addresses per company and as many phone numbers per address without having to change your SQL schema in the future