Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question.

Asked: July 18, 2016 - 1:24 pm UTC

Last updated: July 18, 2016 - 3:23 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi,

I have a table that looks like the following
PHOTOID PHOTO PRODUCT_ID
1 BLOB 123
2 BLOB 123
3 BLOB 123
4 BLOB 123
5 BLOB 123
6 BLOB 124
7 BLOB 124
8 BLOB 125
9 BLOB 126
10 BLOB 126
11 BLOB 126
12 BLOB 126
13 BLOB 126
each PRODUCT_ID can have up to 5 photos

I want to write a statement to create the following
productid photo1 photo2 photo3 photo4 photo5
123 BLOB BLOB BLOB BLOB BLOB
124 BLOB BLOB
125 BLOB
126 BLOB BLOB BLOB BLOB BLOB

any ideas? I have used pivot in the past but this seems to be more complicated.
your help will be much appreciated

and Chris said...

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.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.