Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  General SQL Server Forums  New to SQL Server Programming  Pivot table syntax

Author  Topic  OWSQL Starting Member 27 Posts Posted - 2011-07-17 : 05:38:45 Hi I have created a pivot table based on a quantity coloumn as showed in the code below. I would like to have an additional pivot on price and BUY/SELL. The result I am looking for is a table with the columns BUY Quantity, Sell Quantity, BUY Price and Sell Price for each product and begtime. I now have the first 2 columns and I am wondering wether it is possible to have the last 2 included as well and what the syntax would be like? Thanks in advance. GO Declare @ValuationT datetime; Set @ValuationT = '2011-07-14 23:30:00.000'; --INDTAST Report date Declare @BegT datetime; Set @BegT = '2011-07-01 00:00:00.000'; --INDTAST Begin date Declare @EndT datetime; Set @EndT = '2028-02-01 00:00:00.000'; --INDTAST End date --Futures BUY select * from ( SELECT valuationdetail.tradetype tradetype, position.positiontype, valuationdetail.product product, valuationdetail.begtime begtime, finposition.contractquantity, --valuationdetail.price, valuationdetail.strikeprice strikeprice, position.optionstyle optionstyle, position.optiontype optiontype FROM valuationdetail, position, valuation, trade, finposition, product WHERE valuation.valuationmode = 'Position' and valuation.valuation=valuationdetail.valuation and valuationdetail.position=finposition.position and valuationdetail.posdetail=finposition.posdetail and valuationdetail.product = product.product and valuationdetail.position = position.position and valuationdetail.quantitytype <> 'LOSS' and ( (valuation.valuationtype = 'SUMMATION' and valuation.valuationtime = @ValuationT) or (valuation.valuationtype = 'INCREMENTAL' and valuation.valuationtime >= @ValuationT and valuation.valuationtime <= @ValuationT) ) and position.trade=trade.trade and position.trade is not null AND ((valuationdetail.begtime< @EndT OR valuationdetail.begtime is null) AND (valuationdetail.endtime> @BegT OR valuationdetail.endtime is null)) AND ( trade.status = 'ACTIVE' AND valuationdetail.tradebook IN ('IRM Fuel Prop 1','IRM Fuel Prop 2','IRM Hedging','IRM Proprietary') AND valuationdetail.tradetype IN ('Futures') ) group by valuationdetail.tradetype,valuationdetail.product,position.positiontype,valuationdetail.begtime,valuationdetail.strikeprice, position.optionstyle,position.optiontype,finposition.contractquantity,valuationdetail.price ) DataTable PIVOT (sum(contractquantity)FOR positiontype in ([BUY], [SELL]) ) Pivottable results: tradetype product begtime strikeprice optionstyle optiontype BUY SELL Futures ICE Brent Futures 01-07-2011 0 NULL NULL 1555 1526 Futures ICE Brent Futures 01-08-2011 0 NULL NULL 558 481 Futures ICE Brent Futures 01-09-2011 0 NULL NULL 941 891 Futures ICE Brent Futures 01-10-2011 0 NULL NULL 201 193 Futures ICE Gasoil Futures 01-07-2011 0 NULL NULL 16466 13319 Futures ICE Gasoil Futures 01-08-2011 0 NULL NULL 6599 9492