Pivot in SQL? (1 Viewer)

AccessNub

Registered User.
Local time
Today, 11:42
Joined
Jul 22, 2006
Messages
71
I am normally able to figure this stuff out on my own, but I can't seem to get the logic for a SQL pivot.

Here is what I have in Access:

Code:
TRANSFORM Sum(Table.PaymentAmt) AS Amt
SELECT RptDate
FROM Table
GROUP BY Table.RptDate
ORDER BY Table.RptDate DESC 
PIVOT Table.PlanType;

Which outputs like this:
RptDate Plan1 Plan2
2019-08-25 $436.00 $4,255.24
2019-08-24 $1,167.38 $4,965.16

Can anyone SQLize this? I'm too embarassed to post what I have been trying to do in SQL, it looks like a pile of dog crap.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:42
Joined
Oct 29, 2018
Messages
21,358
Hi. Not sure I understand the question. Isn’t the code you posted above already the SQL representation of your pivot?
 

AccessNub

Registered User.
Local time
Today, 11:42
Joined
Jul 22, 2006
Messages
71
Ah, I wasn't clear. I want this query to work in Sql Management Studio. It doesn't like the word Pivot.
 

AccessNub

Registered User.
Local time
Today, 11:42
Joined
Jul 22, 2006
Messages
71
Ok, I got it, I sat down and really focused on how to build this damn thing..Turns out in SQL you have to name all the possible values.


SELECT RptDate,[PR] as PR, [TR] as TR,[TY] as TY FROM (SELECT RptDate,PlanType, PaymentAmt from TABLE) ps
PIVOT (Sum (PaymentAmt) for PlanType IN ([PR], [TR], [TY])) as pvt ORDER BY RptDate DESC
 

isladogs

MVP / VIP
Local time
Today, 18:42
Joined
Jan 14, 2017
Messages
18,186
Glad you now have a solution ….but are you sure you need to 'alias' fields as the same name?
Did you look at the dynamic pivot link in that article?
 

AccessNub

Registered User.
Local time
Today, 11:42
Joined
Jul 22, 2006
Messages
71
I actually did, it failed without it. I even had to alias the pivot and the table even though I wasn't referencing it anywhere else.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:42
Joined
Oct 29, 2018
Messages
21,358
Ok, I got it, I sat down and really focused on how to build this damn thing..Turns out in SQL you have to name all the possible values.

SELECT RptDate,[PR] as PR, [TR] as TR,[TY] as TY FROM (SELECT RptDate,PlanType, PaymentAmt from TABLE) ps
PIVOT (Sum (PaymentAmt) for PlanType IN ([PR], [TR], [TY])) as pvt ORDER BY RptDate DESC

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom