Data from Row to Coloum - SQL Server Forums

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

Data from Row to Coloum

Author

Topic

OWSQL
Starting Member

27 Posts

Posted - 2011-07-15 : 03:59:36

Hi
I need some inspiration on how to change my dataoutput below based on the below sql. Currently I have 2 rows that I would like to be presented as 1 row. The BUY SELL I would like to have in 2 seperate coloumns. I am considering a temporarily table what I am not sure. Any ideas?
Thanks in advance!

tradetype positiontype product begtime Lots2 avgpr2 strikeprice optionstyle optiontype
Futures BUY ICE Brent Futures 01-07-2011 1241 121.244254 0 NULL NULL
Futures SELL ICE Brent Futures 01-07-2011 1241 120.741265 0 NULL NULL
Futures BUY ICE Brent Futures 01-08-2011 495 114.443636 0 NULL NULL
Futures SELL ICE Brent Futures 01-08-2011 495 114.214868 0 NULL NULL
Futures BUY ICE Brent Futures 01-09-2011 1160 114.56356 0 NULL NULL

from this code:
--use AllegroMRTesting
use AllegroPROD
GO
Declare @ValuationT datetime;
Set @ValuationT = '2011-07-13 23:00: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
SELECT
valuationdetail.tradetype tradetype,
position.positiontype,
valuationdetail.product product,
valuationdetail.begtime begtime,
sum(finposition.contractquantity)/2 as Lots2,
sum(valuationdetail.price*finposition.contractquantity) / sum(finposition.contractquantity/2) as avgpr2,
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
union

lappin
Posting Yak Master

182 Posts

Posted - 2011-08-01 : 11:50:21

To change a row to two columns you can use MIN and Max. Here is a simple example. You could replace "TableName" below with your entire select statement inside brackets - i.e. as a subquery
SELECT ID,
MIN(positiontype) AS Pos_Buy,
NULLIF(MAX(positiontype ), MIN(positiontype)) AS Pos_Sell
FROM TableName
GROUP BY ID
ORDER BY ID

Alternatively use CASE eg
SELECT distinct
valuationdetail.tradetype tradetype,
CASE When position.positiontype ='Buy' then 'Buy' else null end as Pos_Buy,
CASE When position.positiontype ='Sell' then 'Sell' else null end as Pos_Sell, ......etc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources