DROP TABLE normalized_data;

CREATE TABLE normalized_data
(product_id NUMBER,
file_id NUMBER,
file_name VARCHAR2(15),
file_date DATE);

BEGIN
   INSERT INTO normalized_data
        VALUES (50, 1, 'bob.jpg', TO_DATE ('16-MAY-2008', 'DD-MON-YYYY'));

   INSERT INTO normalized_data
        VALUES (50, 2, 'richard.jpg', TO_DATE ('02-APR-2007', 'DD-MON-YYYY'));

   INSERT INTO normalized_data
        VALUES (50, 3, 'orion.jpg', TO_DATE ('25-OCT-2006', 'DD-MON-YYYY'));

   INSERT INTO normalized_data
        VALUES (50, 4, 'blah.jpg', TO_DATE ('09-JAN-2010', 'DD-MON-YYYY'));

   INSERT INTO normalized_data
        VALUES (50, 5, 'proposal.jpg',
                TO_DATE ('06-DEC-2027', 'DD-MON-YYYY'));

   INSERT INTO normalized_data
        VALUES (50, 6, 'sirenl.jpg', TO_DATE ('18-DEC-1982', 'DD-MON-YYYY'));

   INSERT INTO normalized_data
        VALUES (75, 1, 'asdf.jpg', TO_DATE ('27-AUG-2027', 'DD-MON-YYYY'));

   INSERT INTO normalized_data
        VALUES (75, 5, '2asdf.jpg', TO_DATE ('10-APR-2038', 'DD-MON-YYYY'));

   INSERT INTO normalized_data
        VALUES (75, 6, 'k4.jpg', TO_DATE ('11-APR-2038', 'DD-MON-YYYY'));
END;

COMMIT

SELECT *
  FROM normalized_data;
 
SELECT DISTINCT product_id,
                MAX (DECODE (x.file_id, 1, x.file_name)) file_name1,
                MAX (DECODE (x.file_id, 1, x.file_date)) file_date1,
                MAX (DECODE (x.file_id, 2, x.file_name)) file_name2,
                MAX (DECODE (x.file_id, 2, x.file_date)) file_date2,
                MAX (DECODE (x.file_id, 3, x.file_name)) file_name3,
                MAX (DECODE (x.file_id, 3, x.file_date)) file_date3,
                MAX (DECODE (x.file_id, 4, x.file_name)) file_name4,
                MAX (DECODE (x.file_id, 4, x.file_date)) file_date4,
                MAX (DECODE (x.file_id, 5, x.file_name)) file_name5,
                MAX (DECODE (x.file_id, 5, x.file_date)) file_date5,
                MAX (DECODE (x.file_id, 6, x.file_name)) file_name6,
                MAX (DECODE (x.file_id, 6, x.file_date)) file_date6
           FROM (SELECT   *
                     FROM normalized_data
                 ORDER BY product_id, file_id) x
       GROUP BY product_id