How to transpose rows in a table to columns (using outer apply, dynamic query )?

Question

I have an Order Table and a OrderRow table in MS-SQL DB.

OrderNO *(Order)*
--------       
100
101
102 

Product    Qty  **(OrderRow)**
----      ---
Item1     25
Item2     50
Item3      3
Item4     10
----- n items 

I want to write a select query which will return a result like this.

OrderNo   Item1   Item2    Item3   Item4
----------------------------------------------
 100         25      50       3      10 

Currently my Query is below.

Select Order.OrderNo,[Item1].Qty , [Item2].Qty from Order  
Outer Apply 
(Select  Qty  from OrderRow where Order.OrderNo=OrderRow.OrderNo
                                  and Product=Item1
            ) as [Item1]
Outer Apply 
(Select  Qty  from OrderRow where Order.OrderNo=OrderRow.OrderNo
                                  and Product=Item2
            ) as [Item2]

But problem is that the number items in the OrderRow is not a fixed number.

How i can qrite a query that will work for any number of items ?..

Answer 1

If the number of rows were fixed and known, you could use PIVOT/UNPIVOT to achieve this. However, with your kind of problem, the only possible way is to implement a dynamic query and assign the item name as the column alias. You can learn more about dynamic pivot generators here.