Help on PIVOT Query

Question

Post reply

Help on PIVOT Query

ramrajan

SSCommitted

Points: 1737
More actions
July 2, 2014 at 1:10 am

#396992

Create table #temp (ID int , orderdate datetime , Orderamt float , Ordertype int )
Insert into #temp Values (1,'1/1/2014', 500, 1)
Insert into #temp Values (1,'2/2/2014', 100, 1)
Insert into #temp Values (2,'2/2/2014', 100, 2)
--For Ordertype 1 We always have 2 records with the same ID Number 1 , 2 .. etc
--For Ordertype 2 we have only one record for each ID
-- Now i want to display My result As
ID CurrentAmt PrevoiusAmt
1 100 500
2 NULL 100

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

the following article may help you

http://www.sqlservercentral.com/articles/T-SQL/63681/

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

ramrajan SSCommitted Points: 1737 More actions · Answer 2

I am not sure really how PIVOT work can anyone help me to get the desired result set

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

try this.....

with cte as (

SELECT ID

, COUNT(*) AS cnt

, MIN(orderdate) mind

, MAX(orderdate) AS maxd

FROM #TEMP

GROUP BY ID)

SELECT cte.ID

, t2.Orderamt AS CurrentAmt

, t1.Orderamt AS PreviousAmt

FROM cte

INNER JOIN #TEMP AS t1

ON cte.ID = t1.ID AND cte.mind = t1.orderdate

LEFT JOIN #TEMP AS t2

ON cte.ID = t2.ID AND cte.maxd = t2.orderdate AND cte.cnt = 2

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

There's no need to read the table trice.

WITH CTE AS(

SELECT ID,

Orderamt,

ROW_NUMBER() OVER( PARTITION BY ID ORDER BY orderdate) rn

FROM #temp

)

SELECT ID,

MAX( CASE WHEN rn = 2 THEN Orderamt END) CurrentAmt,

MAX( CASE WHEN rn = 1 THEN Orderamt END) PreviousAmt

FROM CTE

GROUP BY ID

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 5

Luis Cazares (7/2/2014)
There's no need to read the table trice.
WITH CTE AS(
SELECT ID,
Orderamt,
ROW_NUMBER() OVER( PARTITION BY ID ORDER BY orderdate) rn
FROM #temp
)
SELECT ID,
MAX( CASE WHEN rn = 2 THEN Orderamt END) CurrentAmt,
MAX( CASE WHEN rn = 1 THEN Orderamt END) PreviousAmt
FROM CTE
GROUP BY ID

fair comment......:-)

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day