Combine Multiple Order rows into Single Row

I have an Order table in this project as

OrderNo, ProductCode

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.)

Many thanks.

Answers


Using PIVOT and Row_Number() function we can achieve this. See the Query below

SELECT [OrderNo],[1] as Product1 ,[2] as Product2,[3] as Product3,[4]  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 ([1],[2],[3],[4])
)Pivottable

See the output image

and here is how my Order table looked like


Need Your Help

onblur/onfocus textarea default value entering into db php

php jquery html database

I have seen this talked about but never answered. Maybe it has and I'm not sure where to find it.

Implement keyDown in android

android key back

I am making an app in which i have to use