CODEWITHSUNDEEP
.netsql-servertransposeouter-apply
Happiness
Happiness

Reputation: 21

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

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

Upvotes: 0

Views: 328

Answers (1)

Saeid
Saeid

Reputation: 1663

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.

Upvotes: 0

Related Questions