MySQL copy multiple rows values with same ID into new columns?

ItemID    |    File                    

1           /storage/somefile1.jpg
1           /storage/somefile2.jpg
1           /storage/somefile3.jpg
1           /storage/somefile5.jpg  

2           /storage/somerandomfile.jpg
2           /storage/anotherrandomfile.jpg
2           /storage/yetanotherrandomfile.jpg开发者_Go百科
2           /storage/somefile.jpg  

I am wanting to create a new column for each file rather than having 1 row per file. Such as:

ItemID    |    File                     |    File2                        | File3 etc...

1           /storage/somefile1.jpg      |   /storage/somefile2.jpg        |   /storage/..


2           /storage/somerandomfile.jpg |  /storage/anotherrandomfile.jpg | /storage/..

Is there any way to automate this with a query?

After seeing your comment and that you're just trying to make a CSV out of it, you can do something like this:

<?php
$query = $db->query('SELECT ItemID, GROUP_CONCAT(File SEPARATOR \'|$|\') AS Files FROM Table GROUP BY ItemID');
// Use a string that cant appear as part of the filename as the separator
$fh = fopen('items.csv', 'w');
foreach ($query->fetchAll(PDO::FETCH_ASSOC) as $row) {
    $files = explode('|$|', $row['Files']);
    fputcsv($fh, array_merge(array($row['ItemID']), $files));
}
fclose($fh);

No. You can't do it(change mysql scheme) on fly.

Following my comment on the question, you can create a new table with your desired schema, and run something like that (assuming PDO):

<?php
$db->beginTransaction();
$query = $db->query('SELECT ItemID, GROUP_CONCAT(File SEPARATOR \'|$|\') AS Files FROM Table GROUP BY ItemID');
// Use a string that cant appear as part of the filename as the separator
$sth = $db->prepare('INSERT INTO NewTable (ItemID, File1, File2, File3) VALUES (:ItemID, :File1, :File2, :File3)');
foreach ($query->fetchAll(PDO::FETCH_ASSOC) as $row) {
    $files = explode('|$|', $row['Files']);
    $sth->execute(array(
        'ItemID' => $row['ItemID'],
        'File1' => isset($files[0]) ? $files[0] : NULL,
        'File2' => isset($files[1]) ? $files[1] : NULL,
        'File3' => isset($files[2]) ? $files[2] : NULL,
    ));
}
$db->commit();

However, I do think your current schema design is better. Why are you trying to change it?

If you want to generate your CSV right in SQL, you can use the following query as starting point:

SELECT CONCAT_WS(',', ItemID, GROUP_CONCAT(File ORDER BY File SEPARATOR ',')) AS FullCSVRow
FROM ItemToFile
GROUP BY ItemID

... which returns:

FullCSVRow
1,/storage/somefile1.jpg,/storage/somefile2.jpg,/storage/somefile3.jpg,/storage/somefile5.jpg
2,/storage/somerandomfile.jpg,/storage/somerandomfile.jpg,/storage/somerandomfile.jpg,/storage/somerandomfile.jpg
3,/storage/file-1-of-2.jpg,/storage/file-2-of-2.jpg

And if you are using PHP you have full control on what queries you do and how you build them. You don't need to change the DB design.

SELECT
   ItemID,

   ( SELECT t2.File FROM ( SELECT @row := @row + 1 AS r, File FROM my_table AS t3
     JOIN ( SELECT @row := 0 ) AS init
     WHERE t1.ItemID = t3.ItemID ORDER BY t3.File ) AS t2 WHERE t2.r = 1 ),

   ( SELECT t2.File FROM ( SELECT @row := @row + 1 AS r, File FROM my_table AS t3
     JOIN ( SELECT @row := 0 ) AS init
     WHERE t1.ItemID = t3.ItemID ORDER BY t3.File ) AS t2 WHERE t2.r = 2 ),

   ( SELECT t2.File FROM ( SELECT @row := @row + 1 AS r, File FROM my_table AS t3
     JOIN ( SELECT @row := 0 ) AS init
     WHERE t1.ItemID = t3.ItemID ORDER BY t3.File ) AS t2 WHERE t2.r = 3 ),

   ( SELECT t2.File FROM ( SELECT @row := @row + 1 AS r, File FROM my_table AS t3
     JOIN ( SELECT @row := 0 ) AS init
     WHERE t1.ItemID = t3.ItemID ORDER BY t3.File ) AS t2 WHERE t2.r = 4 )

   FROM my_table AS t1