Tranpose several rows to columns in SQL

Question

I have two tables.

Order                Order_Items
=====                ===========
order_id             order_item_id  
customer_id          order_id
                     item_id
                     qty
                     price

Can a view be made which returns all items for an order in one row? Only up to 5 items maybe

Ex.

order_id customer_id item_id_1 qty_1 price_1 item_id_2 qty_2 price_2 ....
====================================================================
7845       235         123       1    25.00    NULL     NULL  NULL
7846       784         346       8     9.99     123        1   25.00

http://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql — Dinesh Rajan, May 22 '14 at 18:02
Yakshemash ! As @BogdanSahlean have mention, if you have a variable number of items, then you need to change use dynamic sql to do the pivoting. Chenqui. удача. — Erran Morad, May 22 '14 at 19:11
Are there any drawbacks regarding performance or anything else with dynamic SQL? — Carlos Blanco, May 22 '14 at 20:23

score 1 · Accepted Answer · answered May 22 '14 at 18:22

One way of doing it is to use a common table expression to rank the rows, and then just self join the table 5 times to get each item. You need to use LEFT JOIN to make sure that you also get orders that don't have all 5 slots filled. The query below just implements 3 items, the next 2 are fairly straight forward;

WITH cte AS (
  SELECT a.order_id, a.customer_id, b.item_id, b.quantity, b.price,
  ROW_NUMBER() OVER (PARTITION BY a.order_id ORDER BY order_item_id) rn
  FROM orders a
  JOIN order_items b
    ON a.order_id = b.order_id
)
SELECT DISTINCT a.order_id, a.customer_id,
       b.item_id item_id_1, b.quantity qty_1, b.price price_1,
       c.item_id item_id_2, c.quantity qty_2, c.price price_2,
       d.item_id item_id_3, d.quantity qty_3, d.price price_3
FROM      cte a
JOIN      cte b ON a.order_id = b.order_id AND b.rn=1
LEFT JOIN cte c ON a.order_id = c.order_id AND c.rn=2
LEFT JOIN cte d ON a.order_id = d.order_id AND d.rn=3

An SQLfiddle to test with.

score 1 · Answer 2 · answered May 22 '14 at 18:37

if your order line items are numbered sequentially from 1, you simply do something like this:

select customer_id = o.customer_id ,
       order_id    = o.order_id    ,
       item_id_1   = t1.item_id    , qty_1 = t1.qty , price_1 = t1.price ,
       item_id_2   = t2.item_id    , qty_2 = t2.qty , price_2 = t2.price ,
       item_id_3   = t3.item_id    , qty_3 = t3.qty , price_3 = t3.price ,
       item_id_4   = t4.item_id    , qty_4 = t4.qty , price_4 = t4.price ,
       item_id_5   = t5.item_id    , qty_5 = t5.qty , price_5 = t5.price ,
from dbo.Order      o
left join dbo.Order_Item t1 on i.order_item_id = 1 and i.order_id = o.order_id
left join dbo.Order_Item t2 on i.order_item_id = 2 and i.order_id = o.order_id
left join dbo.Order_Item t3 on i.order_item_id = 3 and i.order_id = o.order_id
left join dbo.Order_Item t4 on i.order_item_id = 4 and i.order_id = o.order_id
left join dbo.Order_Item t5 on i.order_item_id = 5 and i.order_id = o.order_id

If not , you'll need to do the CTE route.

Tranpose several rows to columns in SQL

2 Answers2