Combine Multiple Order rows into Single Row
I have an Order table in this project as
and they refer to OrderDetails and ProductDetails table respectively for details.
The single Order can hold multiple products (say max 4 here) but have separate row for each product ordered.
Thus, data could be like:
OrderNo ProductCode -------------------- 1 Gadget1 1 CaseCover1 1 Cable1
I have a requirement of showing report which looks like:
OrderNo ProductName1 ProductName2 ProductName3 ProductName4 1 Gadget1 CaseCover1 Cable1 NULL
It is not concatenation, but showing different columns.
Please help me, how do I achieve this with Minimal or no Joins. (Objective here is to run query as fast possible. There are thousands of Order records. Inserts and Selects happen every second.)
Using PIVOT and Row_Number() function we can achieve this. See the Query below
SELECT [OrderNo], as Product1 , as Product2, as Product3, as Product4 FROM ( SELECT [OrderNo], Row_Number() OVER (PARTITION BY [OrderNo] ORDER BY ProductCode desc)as ProductNumber, [ProductCode] FROM [Order] ) Source PIVOT ( MAX(ProductCode) FOR ProductNumber in (,,,) )Pivottable
See the output image
and here is how my Order table looked like