How to combine data from one table where there are duplicate values into a single row?

Question

I have tried what i thought was a pretty simple sql with php to do the following but it is not turning out correctly and i cant find a good way to "google" the solutions. I have a table valoreguide2 that holds over 200,000 records with multiple columns in this table are duplicate rows that have different values in some columns but same value in other columns. I want to take all of the values and put them in a new table, while doing this i want to combine the rows that have the same value in the column labeled isbn and keep all of the values for those rows in one row in the new table.

example is

if you cant see the image it is a table with 20 columns including a auto increment field. I would like to take all of the information from this table and insert it into another table with different column titles but combine them based on isbn. the code i have written is a follows:

`$results = $conn->query("select * from valoreguide2 group by isbn");
while ($row12 = $results->fetch_assoc()) {
$isbn = $row12['isbn'];
$APrice =$row12['Acomm'];
$AQty=$row12['Aqty'];
$IPrice =$row12['Icomm'];
$IQty=$row12['Iqty'];
$SPrice =$row12['Scomm'];
$SQty=$row12['Sqty'];
$TPrice =$row12['Tcomm'];
$TQty=$row12['Tqty'];
$NPrice =$row12['Ncomm'];
$NQty=$row12['Nqty'];
$BBPrice =$row12['BBcomm'];
$BBQty=$row12['BBqty'];  



$guide_prices = array('amtext'=>(float)($APrice), 'ingram'=>(float)($IPrice), 'sterling'=>(float)($SPrice), 'tichenor'=>(float)($TPrice), 'nebraska'=>(float)($NPrice), 'BB'=>(float)($BBPrice), );

$bestGuidePrice = max($guide_prices);
$bestGuidePrice = number_format($bestGuidePrice,2,'.','');

foreach ($guide_prices as $key => $val) {
if ($val == max($guide_prices))
{
    $bestGuide = $key;
}

  }

 $valoreprice=(($bestGuidePrice/1.15)-5);

$conn->query("insert into valorebest2 (isbn, aprice, iprice, sprice, tprice, nprice, bookbyte, bestprice, valore, bestguide) values ('$isbn','$APrice','$IPrice','$SPrice','$TPrice','$NPrice','$BBPrice','$bestGuidePrice','$valoreprice','$bestGuide') ");
}`

but the result is not combining the rows it is just picking one.. If i have not provided enough please let me know.. i did not want to type on and on when someone is going to say hey you missed a comma.

Edited:

Using MySQL

Edited: This is the output i am looking for

Answer 1

If it wont let you select, how on earth can you get any values to work with? :-)

It you'll succeed in getting acces to the data, this would do it (notice: the fake_time column is just a way to emulate two different situations in the input table. You can remove it from the select/update if you'll go live with the code):

CREATE TABLE tbs_test_input (ID, ISBN, A, B, C, D, Fake_time) AS
SELECT  1,'0001','A','B','C',10,1 FROM DUAL UNION ALL
SELECT  2,'0002','A',null,null,null,1 FROM DUAL UNION ALL
SELECT  3,'0002',null,'B',null,null,1 FROM DUAL UNION ALL
SELECT  4,'0002',null,null,'C',null,1 FROM DUAL UNION ALL
SELECT  5,'0003','A',null,'C',10,1 FROM DUAL UNION ALL
SELECT  6,'0003',null,null,null,10,1 FROM DUAL UNION ALL
SELECT  7,'0004',null,null,'C',10,1 FROM DUAL UNION ALL
SELECT  8,'0003',null,'B',null,null,2 FROM DUAL UNION ALL
SELECT  9,'0003',null,'B',null,20,2 FROM DUAL UNION ALL
SELECT 10,'0004','A',null,null,null,2 FROM DUAL
;

create table tbs_test_output as select ISBN, A, B, C, D  

from tbs_test_input where 1=2;

insert into tbs_test_output
select isbn, min(A), min(B), min(C), min(D)
  from tbs_test_input 
 where isbn not in (select isbn from tbs_test_output)
   and fake_time = 1
group by isbn
order by isbn;

select * from tbs_test_output;

update tbs_test_output o
set (A,B,C,D) = (
select min(i.A), min(i.B), min(i.C), min(i.D)
  from tbs_test_input i
  where i.fake_time = 2
 group by i.isbn
 having i.isbn = o.isbn
)
WHERE EXISTS (
    SELECT 1
      FROM tbs_test_input i
     WHERE i.isbn = o.isbn 
       and i.fake_time = 2)
;

select * from tbs_test_output;

The result:

4 rows inserted.

ISBN A B C D \---- - - - ---------- 0001 A B C 10 0002 A B C 0003 A C 10 0004 C 10

2 rows updated.

ISBN A B C D \---- - - - ---------- 0001 A B C 10 0002 A B C 0003 B 20 0004 A

Answer 2

Can you be specific about the database, you use: Oracle, MS SQL, MySql ..?

Here is an answer that it based on ansi SQL. Hope it helps

Testdata.
CREATE TABLE tbs_test (ID, ISBN, A, B, C, D) AS SELECT 1,'0001','A','B','C',10 FROM DUAL UNION ALL SELECT 2,'0002','A',null,null,null FROM DUAL UNION ALL SELECT 3,'0002',null,'B',null,null FROM DUAL UNION ALL SELECT 4,'0002',null,null,'C',null FROM DUAL UNION ALL SELECT 5,'0003','A',null,'C',10 FROM DUAL UNION ALL SELECT 6,'0003',null,null,null,10 FROM DUAL UNION ALL SELECT 7,'0004',null,null,'C',10 FROM DUAL;

This select groups by isbn, taking the min-value of each column, that resides in a row with the same isbn.
select isbn, min(A), min(B), min(C), min(D) from tbs_test group by isbn order by isbn;

This select groups by isbn, takes min-value of A,B,C and sums D
select isbn, min(A), min(B), min(C), sum(D) from tbs_test group by isbn order by isbn;

Hope this helps :-)