How to select duplicates into new columns? I am trying to select distinct data in new columns!

My Table

productID | imageURL
1         |    a1.jpg       
1         |    a2.jpg
2         |    a3.jpg


Target Table

productID | imageURL1    | imageURL2    | imageURL3         
1         |    a1.jpg    | a2.jpg       | null
2         |    a3.jpg    | null         | null    


I only want 3 images even if I have 100 in my table.
Subqueries are the easiest solution for the problem, I think.

You can so a simple select or create a new table with CREATE TABLE ... FROM SELECT ...:

select product_id,
    (select image_url from images as images_1 where images_1.product_id = images_o.product_id limit 0, 1) as image_url_1,
    (select image_url from images as images_2 where images_2.product_id = images_o.product_id limit 1, 1) as image_url_2,
    (select image_url from images as images_3 where images_3.product_id = images_o.product_id limit 2, 1) as image_url_3
    from images as images_o
    group by product_id 
    limit 10


Try it here: http://sqlfiddle.com/#!9/bc915d/1/0