I have the following table:
Product Price($) Qty SellingDate
Book 10 10 1/10/2018
Book 15 20 1/10/2018
Pens 1 100 1/10.2018
Pens 2 50 1/10/2018
Misc 150 250 1/10/2018
I have sales price and qty of different products. I would like to perform a pivot to get output in the following format:
Book Pens Misc
(Price*Qty) (Price*Qty) (Price*Qty)
I can have more or less number/type of products sold in a day. I am maintaining the products in a product master. Hence I would like to implement a dynamic pivot.
I tried the pivot query as below:
select * from
(select product, (price*qty ) amnt
from Sales_data where sellingdate = '1/10/2018')
PIVOT (MIN(amnt) FOR product IN (select distinct product from product_master);
When I execute the above query I get the "missing expression" error.
From what I have read, I have understood that dynamic pivot can be performed using XML and the output can then be extracted from the xml output.
However, to extract the result in a readable format, I would still need to know the number of returned columns.
Is there any way to implement dynamic pivot without having to generate XML output?
