How do I "pivot" the top 5 reviews for a product into columns?

Question

I need to (safely) handle the case where there is < 5 "reviews" for a product (even none). In addition, being able to define the ordering of the "reviews" is important. I would prefer a solution that would work with both SQL Server and PostgreSQL (with minor modification). I would be willing to use PIVOT as a last resort, but from what I have read it won't do what I want it to do.

Table1
id,product
1, 'Product 1' 2, 'Product 2'

Table2
id, review
1, 'review #1a'
1, 'review #1b'
1, 'review #1c'
1, 'review #1d'
1, 'review #1e' 1, 'review #1f
2, 'review #2a'
2, 'review #2b'
2, 'review #2c'
2, 'review #2d'
2, 'review #2e'
2, 'review #2f'

Result
1, 'Product 1', 'review #1a', 'review #1b', 'review #1c', 'review #1d', 'review #1e'
2, 'Product 2', 'review #2a', 'review #2b', 'review #2c', 'review #2d', 'review #2e'

Answer 1

Note that as the OP presents it, the id column in table2 is a foreign key to table1.

alter table table2 add column rank int; 
-- better reviews have lower ranks

alter table table2  constraint table2idrank unique( id, rank) ; 
-- ranks are unique within a product

-- note, ranks must also be consecutive

create view min_rank_review as 
select id, min(rank) as rank
from table2 
group by id;


create view product_review_pivot as
select a.product, b.review as r1, c.review as r2, d.review as r3, e.review as r4
from
table1 a left outer join table2 b on (a.id = b.id)
join min_rank_review m on (b.id = m.id and b.rank = m.rank)
left outer join table2 c on (c.id = b.id and c.rank + 1 = b.rank)
left outer join table2 d on (d.id = c.id and d.rank + 1 = c.rank)
left outer join table2 e on (e.id = f.id and e.rank + 1 = d.rank);

select * from product_review_pivot;

On edit: I suppose the down-mods are because this didn't work. But replace left outer join min_rank_review m on (b.id = m.id and b.rank = m.rank) with join min_rank_review m on (b.id = m.id and b.rank = m.rank) (and make a few other minor corrections) and it works.

Sorry, my office mate wanted to go home and woudn't give me time to proof-read it. :)

With the following data inserted:

insert into table1 ( product ) values  ('a'), ('b');
insert into table2(id, review, rank) values 
(1, 'r1', 1 ), (1, 'r2', 2), (1, 'r3', 3 ), 
(1, 'r4', 4), (1, 'r5', 5 ), (2, 'rr2', 1);

I get the following ouput (in MySQL 5):

mysql> select * from product_review_pivot;
+---------+------+------+------+------+
| product | r1   | r2   | r3   | r4   |
+---------+------+------+------+------+
|  a      | r1   | r2   | r3   | r4   |
|  b      | rr2  | NULL | NULL | NULL |
+---------+------+------+------+------+

(The solution for non-sequential ranks is left as an exercise.)

Answer 2

Hi Mike,

PIVOT in combination with ROW_NUMBER() will do this. Here's an example from SQL Server's Northwind sample database. If you have any questions about how to adapt it, please ask, and post CREATE TABLE and INSERT statements for the tables and sample data.

I've used a CTE because it's convenient, but it could be rewritten as a derived table.

Note that the use of MAX is only to satisfy the PIVOT syntax requirement of pivoting an aggregate. MIN would work as well.

with Ranked(EmployeeID,Freight,rk) as (
  select 
    EmployeeID,
    Freight,
    row_number() over (
      partition by EmployeeID
      order by Freight desc, OrderID
    )
  from Orders
)
  select EmployeeID, [1],[2],[3],[4],[5]
  from (select * from Ranked where rk <= 5) as T pivot (
    max(Freight) for rk in ([1],[2],[3],[4],[5])
  ) AS P;

Answer 3

Try this (tweaked to refer to the appropriate things):

The idea is... use row_number() to get up to 5 reviews out per product. Then pivot the results, and then do a left join to get product details (including those that don't have any reviews).

with top5reviews as 
(
select * 
from
(
select 
  productid, review
  , row_number() over (partition by productid order by reviewid) as reviewnum
from reviews
) r
where reviewnum <= 5
)
, pivotted as
(
select productid, [1] as review1, [2] as review2, [3] as review3, [4] as review4, [5] as review5
from top5reviews r
 pivot
 (max(review) for reviewnum in ([1],[2],[3],[4],[5])) p
)
select *
from products p
  left join
  pivotted r
  on p.productid = r.productid