May 31, 2012 at 6:24 am
Hi all
I have a table that holds order details in columns.
eg.
OrderNo, Prod1, Prod2, Prod3..........Qty1, Qty2, Qty3.......... Val1, Val2, Val3
I am trying to transpose the data into rows so that you just get:
OrderNo, Product, Qty, Val
There are 10 product columns in each row, so I know I can do it with 10 union all statements, but since there could be 1,000,000 rows it seems like it would be rather slow to do so. I have tried using UNPIVOT, which works great if I unpivot just the product columns, but I can't work out how to unpivot say the product and the qty columns without it duplicating data. I have to umpivot upto 6 sets of columns, when I tried it for 1 order it generated millions of rows (should have been a couple of hundred)
I've never tried unpivot before, so hopefully there's a simple answer.
SELECT transfer_order, (page_number * 10) + RIGHT(prods,2) -10 AS line_no, product, reqd_qty, unit_cost
FROM
(SELECT transfer_order, to_warehouse, to_warehouse_name, transfer_status, from_warehouse, page_number
, product01, product02, product03, product04, product05, product06, product07, product08, product09, product10
, reqd_qty01, reqd_qty02, reqd_qty03, reqd_qty04, reqd_qty05, reqd_qty06, reqd_qty07, reqd_qty08, reqd_qty09, reqd_qty10
, unit_cost01, unit_cost02, unit_cost03, unit_cost04, unit_cost05, unit_cost06, unit_cost07, unit_cost08, unit_cost09, unit_cost10
FROM OrderDetails) p
UNPIVOT (product FOR prods in (product01, product02, product03, product04, product05, product06, product07, product08, product09, product10)) AS prods
UNPIVOT (reqd_qty FOR reqqty in (reqd_qty01, reqd_qty02, reqd_qty03, reqd_qty04, reqd_qty05, reqd_qty06, reqd_qty07, reqd_qty08, reqd_qty09, reqd_qty10)) AS reqqty
UNPIVOT (unit_cost FOR uc in (unit_cost01, unit_cost02, unit_cost03, unit_cost04, unit_cost05, unit_cost06, unit_cost07, unit_cost08, unit_cost09, unit_cost10)) AS uc
WHERE transfer_order = 'Z000012'
AND product <> ''
Thanks
June 1, 2012 at 8:26 am
UNPIVOT will not work for your query, because it will only produce one unpivoted column per statement, and you need three. You can use a CROSS APPLY to make the UNION ALL much more efficient.
SELECT OrderNo, Prod, Qty, Val
FROM YourTable
CROSS APPLY (
SELECT Prod1, Qt1, Val1
UNION ALL
SELECT Prod2, Qt2, Val2
UNION ALL
SELECT Prod3, Qt3, Val3
UNION ALL
SELECT Prod4, Qt4, Val4
UNION ALL
...
) AS LineItems
Since there is no table referenced in the inner query, it uses the value from the outer query and the table is only scanned once instead of once for each Prodn column.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 4, 2012 at 2:44 am
Thanks Drew
That seems a bit easier to maintain and works pretty fast.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply