First you need a column that has the values mapping to the new columns. You can use row_number to assign a number from 1 to 5 for each photo within each product:
row_number() over (partition by product_id order by id) rn
The second problem is trickier to overcome. You can't use BLOBs in group bys or aggregates! So standard pivot won't work.
One way around this is to:
- Pivot the photo id values
- Use scalar subqueries in the select to fetch the photos for each row
create table t (
id int primary key,
photo blob,
product_id int
);
insert into t
select rownum+12, empty_blob(), mod(rownum, 5) from dual
connect by level <= 12;
insert into t
select rownum, empty_blob(), mod(rownum, 5) from dual
connect by level <= 8;
commit;
select product_id,
(select photo from t where id = "1_ID") photo_1,
(select photo from t where id = "2_ID") photo_2,
(select photo from t where id = "3_ID") photo_3,
(select photo from t where id = "4_ID") photo_4,
(select photo from t where id = "5_ID") photo_5
from (
select product_id, id,
row_number() over (partition by product_id order by id) rn
from t
)
pivot (min(id) as id for (rn) in (
1 as "1", 2 as "2", 3 as "3", 4 as "4", 5 as "5")
);
PRODUCT_ID PHOTO_1 PHOTO_2 PHOTO_3 PHOTO_4 PHOTO_5
0 oracle.sql.BLOB@4e6d9eef oracle.sql.BLOB@b6f0946 oracle.sql.BLOB@60bd2cb9
1 oracle.sql.BLOB@3aae8238 oracle.sql.BLOB@1896f964 oracle.sql.BLOB@6b40f609 oracle.sql.BLOB@6211c73c oracle.sql.BLOB@2ce02604
2 oracle.sql.BLOB@392b725 oracle.sql.BLOB@6f0ee9e0 oracle.sql.BLOB@34afb1a oracle.sql.BLOB@2e373d71 oracle.sql.BLOB@380a8c0d
3 oracle.sql.BLOB@20d32305 oracle.sql.BLOB@74958e2e oracle.sql.BLOB@63dee978 oracle.sql.BLOB@fd87bea
4 oracle.sql.BLOB@362aaa1f oracle.sql.BLOB@6e16a60a oracle.sql.BLOB@7a971799
This has many repeated accesses of the table though. So performance won't be great if you've got a large amount of data.